Date Formats in Excel are incorrect

[kiwijo]

have created an app in ASP which prints out timeclock events. It also
saves them to an excel file. Problem is the dates are displayed
correctly on screen and in the printout, however, any date in 2004 is
displayed as mm/dd/yyyy. All dates for 2003 were displayed as per my
locale dd/mm/yyyy.

Changing the following code to use long date format has been the only
solution so far, but I need to use short date format of dd/mm/yyyy.

Response.write "objXLSheet.Range(""" & strColumnVal & intCounter &
""").NumberFormat = ""dd/mm/yyyy"";"
Response.Write "objXLSheet.Range(""" & strColumnVal & intCounter &
""").Value = '" & FormatDateTime(escapeQuote(objRSRetrieve(strRecSetID)),
vbShortDate) & "';"&vblf

 

[kiwijo]

I have just discovered that it is only the dates from 01/01/2004 to
12/01/2004 that are displayed as mm/dd/yyyy, and dates from 13/01/2004
to 31/01/2004 are correct. Any suggestions would be most welcome on
how to stop the 01/01/2004 to 12/01/2004 dates from being displayed as
mm/dd/yyyy.

 

[RWN]

Highlight the "incorrect" dates then;
"Format Cells->Number"
Select "Date" in the left box and choose the format you want in the
right box.
Conversely, if you want to customize the format, rather than select
"Date" select "custom" and find/make whatever you like.
Check out the Help screens for further info.

 

[Ron Rosenfeld]

I have just discovered that it is only the dates from 01/01/2004 to
12/01/2004 that are displayed as mm/dd/yyyy, and dates from 13/01/2004
to 31/01/2004 are correct. Any suggestions would be most welcome on
how to stop the 01/01/2004 to 12/01/2004 dates from being displayed as
mm/dd/yyyy.

Well since no one has answered, I can at least suggest some areas to look at,
based on this behavior. (I'm really not familiar enough with exactly what you
are doing, though).

When you input a value into Excel that looks like a date, Excel will interpret
it according to the Windows Regional Settings. The FORMAT in Excel applies
only to how the value is DISPLAYED.

The type of behavior you describe is frequently due to a mismatch in this area.
If Excel sees a value that "makes sense" according to the Windows Regional
Settings, it interprets it as a date. If it does not, it is probably
interpreting the value as a string (or Text). In the case of the latter, your
formatting of the cell will have no effect on the display.

So to have the data interpreted as a date by Excel, so that you can apply your
desired date formatting, it must be properly input. You can do this either as
a string in the same format as your Windows Regional Settings, or you can parse
the value from the timeclock into a Year, Month and Day and use either math or
the Dateserial function to turn it into a value that excel will properly
recognize as a date. (you might have to do the same thing with the time
values).

I hope these suggestions help.


--ron

 

[donovanm]

Hi kiwijo

I am finding that I'm having a very similar problem. I have a VBA user
form which prompts the user to enter a date which in turn puts that
date into a cell. The VBA worked fine last year, only to return to the
office this year to discover it doesnt work anymore!

If I enter todays date for example (09/01/2004) for some unknown reason
excel swaps around the dd and mm to display 01/09/2004 which is
incorrect!!! However if I enter 13/01/2004 then 13/01/2004 will be
displayed, which is correct!! This is a very frustrating!

I've tried searching the Microsoft Knowledge base, but to no
avail....anyone have any ideas?

 

[donovanm]

I've just found that my problem affects the dates using the first 12
days of EVERY month, not just January.

 

[Ron Rosenfeld]

Hi kiwijo

I am finding that I'm having a very similar problem. I have a VBA user
form which prompts the user to enter a date which in turn puts that
date into a cell. The VBA worked fine last year, only to return to the
office this year to discover it doesnt work anymore!

If I enter todays date for example (09/01/2004) for some unknown reason
excel swaps around the dd and mm to display 01/09/2004 which is
incorrect!!! However if I enter 13/01/2004 then 13/01/2004 will be
displayed, which is correct!! This is a very frustrating!

I've tried searching the Microsoft Knowledge base, but to no
avail....anyone have any ideas?

VBA is very US-centric with regard to date issues. It would not surprise me if
the correctly displayed dates are going in as TEXT, whereas the incorrectly
displayed ones are going in as Dates (and the wrong one's to boot).

But I don't know enough about writing internationally compliant code to give
you further advice.
--ron

 

[Paul]

I've just found that my problem affects the dates using the first 12
days of EVERY month, not just January.

Have you read Ron Rosenfeld's response to the original poster? In this, he
explained that if you type in something Excel can recognise as a date
(according to your Windows regional settings), then it will be stored as a
date. If it cannot be interpreted as a date then it is simply treated as
text.

You appear to have your Windows regional settings set to recognise dates in
the US style (mm/dd/yy or mm/dd/yyyy) even though you apparently consider
(for example) 09/01/2004 to be 9th Jan 2004. In this situation, when you
type in 09/01/2004 Excel interprets this as the date 1st Sep 2004 and so
displays it as 01/09/2004. However, when you type in 13/01/2004 Excel cannot
recognise that as a (US-style) date, so simply displays it as text. You will
be able to see a difference between these two entries if you have no cell
alignment applied (and you column is wide enough) as text will be
left-aligned and dates will be right-aligned. Alternatively, a formula such
as =ISTEXT(A1) will return TRUE for text and FALSE for a date.

I suggest that you use Windows Control Panel to set your Windows regional
settings correctly for UK-style dates, reboot your computer and try again.

 

[Chris R. Lee]

VBA is very US-centric with regard to date issues. It would not surprise me if
the correctly displayed dates are going in as TEXT, whereas the incorrectly
displayed ones are going in as Dates (and the wrong one's to boot).

But I don't know enough about writing internationally compliant code to give
you further advice.

The only international compliant format is the one defined by ISO. This
stately organisation didn't invent anything, because the format has been
used by astronomers for 2 centuries or more.

The only International Compliant Sofware Provider should use this format by
default, or some people will be eating some very mouldy cheese or whatever.
It even makes sense when sorting dates: yyyy-mm-dd.

This is the only unambiguous numeric format around. According to ISO the
only separator allowed is the hyphen, but you can use no separator if the
meaning is clear - for example in a spreadsheet???


Regards

 

[RWN]

The only International Compliant Sofware Provider should use this format by
default, or some people will be eating some very mouldy cheese or whatever.
It even makes sense when sorting dates: yyyy-mm-dd.

This is the only unambiguous numeric format around. According to ISO the
only separator allowed is the hyphen, but you can use no separator if the
meaning is clear - for example in a spreadsheet???


Regards



Exactly!

 

[Ron Rosenfeld]

The only international compliant format is the one defined by ISO. This
stately organisation didn't invent anything, because the format has been
used by astronomers for 2 centuries or more.

The only International Compliant Sofware Provider should use this format by
default, or some people will be eating some very mouldy cheese or whatever.
It even makes sense when sorting dates: yyyy-mm-dd.

This is the only unambiguous numeric format around. According to ISO the
only separator allowed is the hyphen, but you can use no separator if the
meaning is clear - for example in a spreadsheet???

OK. But how does this statement of yours apply to or help the OP's problem?


--ron

 

[kiwijo]

my short date format is set for dd/mm/yyyy, and has been all along. it
is just the first 12 days of each month that are displayed as
mm/dd/yyyy, all the rest of the dates in the month are displayed
according to my regional settings dd/mm/yyyy.

 

[Ron Rosenfeld]

my short date format is set for dd/mm/yyyy, and has been all along. it
is just the first 12 days of each month that are displayed as
mm/dd/yyyy, all the rest of the dates in the month are displayed
according to my regional settings dd/mm/yyyy.

Did you investigate the issues that I and others have raised with regard to
your Windows regional settings and the possibility that some of your entries
may be being interpreted as text?

(I haven't seen any response from you regarding these issues, but may have
missed it).


--ron

 

[kiwijo]

I have done as you suggested. The cells containing the first 12 dates
of each month are TRUE and left aligned, while all others are FALSE
and right aligned. However my regional settings are configured for
dd/mm/yyyy (Australian style format)

An interesting comment which a friend has just sent me is that ASP
sets the locale to US by default. Could that be my issue here?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/script56/html/vsfctsetlocale.asp

 

[excalibr]

Hi all,

We've experienced the same problems, but not all PC's are affected. Th
Region Settings are the same, ie. Australia and dd/MM/yyyy but there'
one PC which incorrectly interprets 01/12/2003 as January 12 200
(31/12/2003 is fine though).

Our problem manifests itself when we open a CSV file using a macr
(though opening the CSV manually does not cause this problem).

Workbooks.Open Filename:="C:\test\test.csv"

Our workaround is to output the date in yyyy/mm/dd format to the CS
file.

What puzzles me is that one out of 5 PC's suffer from this, so I thin
it's a configuration/setting issue. But what? If anyone has prope
solution, we'd love to hear from you.

Excalibu

 

[Guest]

You're right - in your windows control panel under your
regional options / regional settings, you should see a tab
there that says "date" - look at the format on the nice
computers, and copy it down.. now go to the "mad" computer
and change it to be like the other computers show.

Microsoft Excel uses this information when interpreting
dates, so whatever is your long-date and short-date style
in your regional settings, that's the format that will be
displayed.