Date for mat

Deepak - Feb 1, 2010 at 03:29 AM
 Trowa - Feb 2, 2010 at 08:34 AM
After setting date format in Contral Panel, in Excel Cell date shows dd/mm/yy, but in formula bar it shows dd/mm/yyyy. Can anybody tell me how to change date format both in cell and formula bar as dd/mm/yy

Thanks in advance

Deepak Tapse

1 reply

Hi Deepak,

The formulabar shows the content excel will calculate with.

When you enter a number like 2.465795 and format the cell as number with two decimals, the cell will show 2.47 but the formula bar will still show you 2.465795 since that's the number excel will calculate with.

The same goes for dates: if the format is dd/mm/yy and enter 15/02/1950 the cell shows 15/02/50.
Now looking at the cell you can't see the difference between 1950 or 2050. To check this select the cell and look at the formulabar.

So the formulabar can't be formatted.

Best regards,
Thanks Trowa

But my problem is, in fome excel software, when i enter date manually it shows dd/mm/yy format in both cell and formula bar. But, when i copy paste the date from another excel sheet, it shows dd/mm/yy in cell and dd/mm/yyyy in formula bar. When i validate this it shows error due to date format.
Trowa > Deepak
Feb 2, 2010 at 08:34 AM
Strange, I can only suggest you to check the options (extra>options) of both excel version to see if there are any differences.

Unfortunately I am not familiar with the validation option, hopefully someone who does will respond.

Best regards,