excel dates 3

[SQLScholar]

Hey,

Is it possible, that if i have a date such as 20020323 (yyyymmdd) as just a value, to get this to be a date such as dd/mm/yyyy.

I think a formular will be needed to be able to extract the date, but i cant work it out.

TIA

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[ETID]

try this...assuming your date string is in cell A1

=mid(a1,5,2)&"/"&right(a1,2)&"/"&left(a1,4)

 

[ETID]

Ooops...what I gave you will result in mm/dd/yyyy....

try this one instead

=right(a1,2)&"/"&mid(a1,5,2)&"/"&left(a1,4)

 

[SkipVought]

Dan,

Date/Time in Excel is just a number like 37958.30089 which is 12/3/2003 7:13 AM here in north Texas.

That number can be formatted a bunch of ways to DISPLAY

Wednesday (dddd)

Wed, December 3, 2003 (ddd, mmmm, d, yyyy)

07:13 (hh:mm)

12/03/03 7:13 (m/d/yy h:mm)

SAME NUMBER - DIFFERENT FORMAT

That being said, if you enter 20020323, Excel thinks its just a number. If you FORMAT that number as a DATE, Excel STILL thinks it's a number, (like 37958.30089 above, remember?) but it tries to FORMAT a date. In the case of 20020323, it's trying to format a date that is 1/1/1900 + 20020323 days. Well that's 54,850 YEARS from 1900. You, me Bill Gates, Excel ain't gonna be around in the year 56,750.

Long and short of it, the only way to enter a number like 20020323 and have it become a date is with some fancy programming (just consider that the user has already entered 20020323, and the fancy program has converted that to a date, which is 37338. And THEN, someone edites that value -- the fancy program has to recognize thatand NOT change it as well as a slew of other things)





Skip,

http://www.TheOfficeExperts.com

 

[bluedragon2]

Will this work for you:

=DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2))

Blue

If I wasn't Blue, I would just be a Dragon...

 

[SQLScholar]

Etid - thanks for that STAR

Skip - Thanks for the info, always worth barring in mind STAR

Blue - Thats purfect, as Etid - but also formats it to date. Thanks STAR

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!