Default Date Format

**avveerkar** · 06-13-2007, 10:39 PM

In my Regional And Language setting the date format is dd/MM/yyyy and Excel does map the date entered in any cell to this format. eg if I start on a fresh excel sheet and enter 4/5/6 in any cell or I have a code line say [a1]=Cdate("4/5/6"), it displays as 04/05/2006 as expected. If I reformat the cell as dd-mmm-yyyy it correctly shows 04-May-2006 indicating excel has taken 4 as day, 5 as month and 6 as year.
However if my code is [a1]= "4/5/6" then A1 dispalys 4/5/2006 and if I format the cell as dd-mmm-yyyy it shows 05-April-2006. It shows that it tries to map the string to M/d/yyyy format. Same is true with Cstr. If my date is 04-May-2006 , Cstr converts it to the string "4/5/2006". One more observation - if I want to use literal date and try to type in a code line say [a1]= # 4 May 2006 # it automatically changes to [a1]=#4/5/2006# as soon as I complete the line and hit the return.

I have come to a conclusion that Excel has two different formats it tries to map the probable date strings to . One is the Regional setting and the other is M/d/yyyy which seems to be its own default setting. Am I right? If yes, then I want to know if I can have access to this default Excel preferred setting. And can if I change that also to dd/MM/yyyy?

A V Veerkar

**mikerickson** · 06-14-2007, 01:12 AM

Excel gets its default formatting from your operating system's time/date formatting. I use that feature to change the time seperator to something that doesn't need a Shift. It also works with date formatting.

**avveerkar** · 06-14-2007, 02:36 AM

Originally Posted by mikerickson

Excel gets its default formatting from your operating system's time/date formatting. I use that feature to change the time seperator to something that doesn't need a Shift. It also works with date formatting.

I use XP. How do I access the time and date format. I only know how to set date and time but do not know how to access the display format.

Thank you

A V Veerkar

**lyilaly** · 06-14-2007, 08:44 AM

Here's a small code to see date format work, please notice that the format function works for String data type.

Please Login or Register  to view this content.

Try and let me know.

**mikerickson** · 06-14-2007, 08:57 AM

I'm a Mac user. Someone here must be XP wise.

**Richard Schollar** · 06-14-2007, 09:02 AM

VBA only understands US date format ie MDY whereas Excel itself will use the System settings.

Richard

**avveerkar** · 06-14-2007, 01:24 PM

Originally Posted by RichardSchollar

VBA only understands US date format ie MDY whereas Excel itself will use the System settings.

Richard

Richard,

What you say makes sense. VBA understands only US format (M/d/yyyy) but Excel will follow the format set in system Regional and Language panel. And also I understand that there is no way we can change VBA default setting.

A V Veerkar

Default Date Format

LinkBack

Thread Tools

Rate This Thread

Display

Default Date Format

Example

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions