Help ASAP. Changing number string into date?

[John C. Harris, MPA]

I pull reports from an internet site into excel for state reporting purposes
and need to calculate the duration from one date to another. The problem is
when I pull the table in it chages the dates to a string (i.e. 06/02/2001
looks like 6022001, and 12/02/2001 looks like 12022001). Is there a formula
that will take the 6022001 and 12022001 and chane them into date format? I
tried the format tab and it wouldn't work.

TIA

 

[JohnI]

John,

You need to include an example date that shows us where the day/month
appear.
I believe all US dates are m/d/y, but UK, Australian, etc. dates are d/m/y.

Use an example, such as 6302001 for the 30th June for US dates.

regards,

JohnI

 

[John C. Harris, MPA]

OK. The dates are appear as a string.... 6022001 should be June 02, 2001 and
needs to be in the column as 06/02/2001. Same for the two digit
months...they appear as 12022001 for December 12, 2001 and should be
12/02/2001. Hope that makes sense.

 

[JohnI]

John,

Try -
=IF(LEN(A2)=7,DATE(RIGHT(A2,4),LEFT(A2,1),MID(A2,2,2)),IF(LEN(A2)=8,DATE(RIG
HT(A2,4),LEFT(A2,2),MID(A2,3,2)),"Error"))

where A2 is your date. Note if you see the word "Error" then the formula is
not handling those dates correctly.

regards,

JohnI

 

[John C. Harris, MPA]

THAT FORMULA TURNED THE STRING 6022000 (JUNE 2, 2000) INTO 36689?

 

[Alan Beban]

When I did it it returned 36679. Then with 36679 in Cell I10, the
following returned 6/2/2000:

=MONTH(I10)&"/"&DAY(I10)&"/"&YEAR(I10)

Alan Beban

 

[JohnI]

John,

Sorry, forgot to add one more step.

Select the column with the formula & do

- Format - Cells - Select "Number Tab" - Choose your appropriate Date
format.

36689 is the way MS Excel stores the date, i.e. as Days-Since-1900.
Formatting shows the correct date "June 02, 2000".

regards,

JohnI

 

[John C. Harris, MPA]

That did it. Thanks a bunch guys.

John,

Sorry, forgot to add one more step.

Select the column with the formula & do

- Format - Cells - Select "Number Tab" - Choose your appropriate Date
format.

36689 is the way MS Excel stores the date, i.e. as Days-Since-1900.
Formatting shows the correct date "June 02, 2000".

regards,

JohnI


=IF(LEN(A2)=7,DATE(RIGHT(A2,4),LEFT(A2,1),MID(A2,2,2)),IF(LEN(A2)=8,DATE(RIG there

 

[JohnI]

John,

Glad to know it was of help.

Thanks for replying to tell us how you went.

regards,

JohnI