Need to test for 7 digit date in excel add leading 0 if needed

[goitc7]

Hi,
I have a handheld device that I download to Excel. The date column has to end up mm/dd/yyyy but when I convert the column to text I lose leading zeros for singe digit months. I am trying to use a macro to
1. convert to text
2. test for 7 digits and add the 0 if needed
3. then maybe reference the column (for instance C) and display as date (for instance into column D)
4. maybe copy the value of Column D to E and then delete Columns C and D
I have to end up with a column that has Date in row 1 and everything else has to be in date format mm/dd/yyyy
Thank you

 

[macropod]

Hi goitc7,

The loss of the leading 0 is essentially a formatting issue, and no amount of adding leading 0s will change that.

A simple formula to convert you string to a date value is:
=DATEVALUE(TEXT(A1,"00-00-0000"))
You can then format the output cell as a date.

Cheers
Paul Edstein
[MS MVP - Word]

 

[SkipVought]

but when I convert the column to text

Do you REALLY want to comvert your DATE to TEXT? When you do, it is no longer a date.

Real Dates are just NUMBERS, like TODAY is 40987.

You can FORMAT the CELLS on the sheet to DISPLAY whatever format you like, even a custom format via Cell Number Format.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[goitc7]

Thank you all. The reason I have been trying to convert the "date" import column to text is because the single digit months lose the leading 0 by the time it opens in Excel. So I was doing that to put the 0 back in.

The DATEVALUE would be used after it is converted to text and the 0 added, correct?

If so, what is the correct code to check for 7 digits in, say column E and if needed add the 0 else leave it alone?

Thank you all again

 

[goitc7]

When I try the DATEVALUE formula (for instance in K1 for the text in J1) it shows the formula in K1 instead of the value of J1 converted !?!?

 

[PHV]

Can't you set the NumberFormat property of the cells to "mmddyyyy" ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[goitc7]

Hi PHV
NO, that doesn't bring any joy! I have to convert to text to be able to add leading zero if needed and then changing the column to a date format doesn't seem to have any effect.
Thanks

 

[PHV]

Why do you have to convert to text ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Converting your date to text DESTROYS its usefulness as a date.

FORMATTING your date as mm/dd/yyyy does work, as long as what you have in those cells is a real date.

If you CHANGE the FORMAT to GENERAL, you should see NUMBERS. IF you do not then all you have is TEXT.

In that case, here's what to do to convert TEXT to DATE.
[tt]
1. in an empty cell off to the side, enter 1.

2. COPY that cell.

3. SELECT the cells containing the dates that are TEXT.

4. Right-Click > Paste Special -- MULTIPLY.

5. Right-Click > Format Cells - NUMBER Tab ... and select the appropriate format or CUSTOM format .

6. DELETE the 1 that you entered in step 1.
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[goitc7]

Hello all,
Let me clarify ... I am not initially converting a date to text. I am importing a file from a handheld device. The column that contains the data that is the date is, to Excel just some numbers that end up, I think being formatted as general. If the number had a leading 0 coming in, for instance 03192012 Excel leaves me with 3192012.

At first, I was using the text to column method and it was working because at the time I was working with 2 digit months and all was well but when they tried it with 1 digit months that is when it started giving strange output. So, that's what got me here

 

[PHV]

Did you try to set the NumberFormat property of the cells to "00000000"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

[highlight]3192012 is NOT a date[/highlight]. It APPARENTLY is a NUMBER, that has not relation at all to the REAL DATE, which for march 19 2012, is 40987.

What you want is
[tt]
=TEXT(a1,"00000000")
[/tt]
where A1 is your number.

YOU DO NOT HAVE DATES! Just some NUMBER that, to you, represent a date, but is totally useless to use as a REAL DATE.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[goitc7]

Skip, it seems like that should work. Then I can use the text to column routine, right?
So, if I had the imported data in column D, I could use
=TEXT(d1,"00000000")and then copy that down through the column. Next, to import into Access I need to get rid of the extra columns so I need to delete column D should I copy the value of E to F and delete E as well or if I delete D will E maintain the correct data?

 

[SkipVought]

if I delete D will E maintain the correct data?

[tt]
1. Select column E

2. COPY

3. right-click > Paste special -- TEXT
[/tt]
will convert the formulas to TEXT.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[goitc7]

Ah! Thanks
Let me give this a try and I will let you all know

 

[macropod]

Did you actually try the solution I posted? It seems there's been a lot of back & forth without much evidence of trying anything.

Cheers
Paul Edstein
[MS MVP - Word]

 

[goitc7]

Hello all,
Sorry for the delay in sharing the results.

Putting it all together it worked!

Used =DATEVALUE(TEXT(A1,"00-00-0000"))
then pasted the value of this column to a new column and deleted the other two columns used to get to this point.

Works great ... I cannot thank y'all enough!