MS EXCEL CONVERT DATE

[nike23]

In Excel 2000 how do you get the correct date to appear. For example you type in 04010 then format the cell to a date it changes it to 10/18/2009. Is this possible without having to type in the / or .?

 

[SkipVought]

Hi,

Dates in Excel are just numbers ---

like today is 38078.

You can use menu item Format/Cell/Number Tab and either select a Stock Date or format your own CUSTOM date.

For instance 38078 can be formatted
[tt]
Display - Format
Thursday - "dddd"
Thu April 01, 2004 - "ddd mmmm dd, yyyy"
[/tt]



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bluedragon2]

No because in Excel, a date is actually a number. So, what you are entering is a number. Now if you want, you can enter:

'030704

in say cell A1 for 03/07/04, you can then put:

=DATEVALUE(LEFT(A1,2)&"/"&MID(A1,3,2)&"/"&RIGHT(A1,2))

In say B1 and it will give you the date you are looking for.



[Blue]Blue[/Blue] [Dragon]

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

 

[SkipVought]

I should have continued that Excel RECOGNIZES when you enter 4/1/02 that this is a date in the form of your regional settings and determines that 4 is a month, 1 is a day and 02 is really 2002 and CONVERTS all that to a NUMBER and FORMATS the cell to DISPLAY it as a DATE.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884