How to display yy/mm/dd 06/03/04 in excel and not get 06/02/2065!

[Guest]

I need to display dates in excel by yy/mm/dd but when I apply this custom
number format to cell, it messes up and gives me wacky dates..

EX: If I enter 06/03/04 in excel I get get 06/02/2065!! I wonder if this has
to do with 2006 being entered as 06 and the first 0 is not recognized??

Anyone got any suggestions!!

Thanks
PS: The date MUST be separeted with / and not - inorder to be consistant all
the company's programs & reports.

 

[Guest]

Hi

I have tried to recreate your problem but I can't. If I format my cells as
yy/mm/dd and type an Excel date in (such as 04/02/06) it displays the result
correctly - 06/02/04.
What version of Excel are you using and what order are you
formattin/entering the data? (I'm using Excel 2K)

Andy.

 

[edcosoft]

Select the cells you want to format. Go to Format
menu/cells/number/Custom.. Pick any yy/mm/dd format and follow the
directions to overwrite with yy/mm/dd and close window. When you type
1/16/06 in a formatted cell you will read out in the cell 06/01/16 ,
but in the formula bar it will be 1/16/06. It works fine on my
EXcel2003. ed

 

[Guest]

Even if you wnat to display the date as yy/mm/dd, you must type it into the
cell as a standard date. In the US, that would be dd/mm/yy.
If you do that, your fomratting should work.

 

[Ron Rosenfeld]

I need to display dates in excel by yy/mm/dd but when I apply this custom
number format to cell, it messes up and gives me wacky dates..

EX: If I enter 06/03/04 in excel I get get 06/02/2065!! I wonder if this has
to do with 2006 being entered as 06 and the first 0 is not recognized??

Anyone got any suggestions!!

Thanks
PS: The date MUST be separeted with / and not - inorder to be consistant all
the company's programs & reports.

The cell format only affects the DISPLAY of the cell contents.

In order to properly ENTER a date, you must either enter an unambiguous string,
or use the short format of your "Windows" Regional Settings (Start/Control
Panel/Regional Settings).

The cell format in Excel has NOT effect on how the date entry is interpreted.

Given your entry of 06/03/04, I could not tell what date you are trying to
enter. If your Windows Short Date Regional Settings are yy/mm/dd, then Excel
should interpret it as 2006-Mar-03; but if those settings are mm/dd/yy, then
Excel will interpret it as Jun-03-2004.

In addition, I don't believe that you are really entering 06/03/04; rather I
think you are probably entering 060304. While it is possible to enter dates
without separators and have Excel interpret it as a date, to do that requires
either a VBA Macro or a helper column with a formula.

If, indeed, you must enter 060304 and have it be interpreted as the date
2006-Mar-04, then one way would be to put in an adjacent column the formula:

=DATE(INT(A1/10^4)+2000-100*(
INT(A1/10^4)>30),INT(MOD(
A1/10^4,1)*10^2),MOD(A1,10^2))

to convert your number to a date.


--ron