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
"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(sheet1!A1,"general"),4,2),MID(TEXT(sheet1!A1,"general"),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
|
Similar Threads
