Changing Default Date format in excel

[Guest]

Hi
I want excel to dislay date in the format ( dd-mmm-yy) ( like 21-May-04 ) not only for viewing but also
in the FORMULA bar
Its because I am recording a macro which will copy today's date and save the file with
name of the file having today's date in dd-mm-yy format, so that confusion on whether 01-02-04 stands for
Jan 2nd 2005 0r Feb 1st 2005 may not be ther
I used today( ) function and then copied today's date and did paste special in to a blank new cel
Then in the new cell I changed the date to the format dd-mm-yy but the formula bar stil
displays the date in the format mm/dd/yy only.

To change the default date formatting excel I went to control panel -> Regional settings and there I changed th
format for short date style from mm-dd-yy to dd-mmm-yy. But When I go back to excel and check default format fo
date it shows as dd-mm-yy instead of dd-mmm-yy (Interestingly in a blank notepad when I select
edit-> time/date there it displays the same as 2:14 AM 21-May-05 , that is the format in
notepad has changed to dd-mmm-yy but in Excel it is dd-mm-yy

Please suggest a way out of the same. ( I have tried the style option in format menu but it didn
seem to have what I need

Regards
Har
India

 

[Debra Dalgleish]

Since changing the short date style doesn't work, you could format the
date as you save the file. For example:

ActiveWorkbook.SaveAs Filename:= _
"C:\Data\" & Format(Date, "dd-mmm-yyyy") & ".xls"

 

[Dave Peterson]

You don't have much control over what appears in the formula bar.

Try putting 12341243.32 in a cell and giving it the most complex format you can
find:

I used: _($* #,##0.00000_);_($* (#,##0.00000);_($* "-"??_);_(@_)

(just clicked the $ icon and the increase decimal icon.)

Now I see:
$ 12,341,243.32000
In the cell.

But in the formula bar, I still see: 12341243.32.

Same kind of thing happens with dates.