changing date formatted as general number into date format

[Guest]

Hi -
I have a rather large dataset that arrived with all the dates formatted as
general numbers which I can't seem to convert to a date format that is
recognized by either excel or access 07 - I've tried converting to regular
number then to text as well as converting to text and then to date but I
can't seem to make it work. The dates are also represented in yyyymmdd
format - ie, 20070105 which doesn't line up with the typical american date
format. Any suggestions? Any help would be appreciated. Thanks

 

[Guest]

the numbers are proably in straight number format and not date format. On a
new woksheet copy this formul into cell A1. change sheet1 to the correct
worksheet name. then copy this formula to the entire worksheet. see if you
get the correct dates. the fomula changes a general number 20071201 to a
microsoft date number.

=DATE(LEFT(TEXT(sheet1!A1,"general"),4),MID(TEXT(sheet1!A1,"general"),4,2),MID(TEXT(sheet1!A1,"general"),7,2))

 

[Guest]

Ok - will try this and let you know. Thanks!!

the numbers are proably in straight number format and not date format. On a
new woksheet copy this formul into cell A1. change sheet1 to the correct
worksheet name. then copy this formula to the entire worksheet. see if you
get the correct dates. the fomula changes a general number 20071201 to a
microsoft date number.

=DATE(LEFT(TEXT(sheet1!A1,"general"),4),MID(TEXT(sheet1!A1,"general"),4,2),MID(TEXT(sheet1!A1,"general"),7,2))

 

[Guest]

Well - I'm getting a date - but they are all the same 1/0/1900 -

 

[Guest]

JOEL - thanks for your help - I got it to work - tweeked the formula - a tad
but couldn't have done it without your help! Thanks so much!!!!
=DATE(LEFT(TEXT(A2,"general"),4),MID(TEXT(A2,"general"),5,2),MID(TEXT(A2,"general"),7,2))
where the original date in in A2 - changed 4,2 to 5,2