changing date formatted as general number into date format
 

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

changing date formatted as general number into date format
 

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(s heet1!A1,"general"),4,2),MID(TEXT(sheet1!A1,"gener al"),7,2))

"pghio" wrote:

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

changing date formatted as general number into date format
 

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

"Joel" wrote:

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(s heet1!A1,"general"),4,2),MID(TEXT(sheet1!A1,"gener al"),7,2))

"pghio" wrote:

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

changing date formatted as general number into date format
 

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

"Joel" wrote:

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(s heet1!A1,"general"),4,2),MID(TEXT(sheet1!A1,"gener al"),7,2))

"pghio" wrote:

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

changing date formatted as general number into date format
 

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,"gene ral"),5,2),MID(TEXT(A2,"general"),7,2))
where the original date in in A2 - changed 4,2 to 5,2

"pghio" wrote:

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

"Joel" wrote:

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(s heet1!A1,"general"),4,2),MID(TEXT(sheet1!A1,"gener al"),7,2))

"pghio" wrote:

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