Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
 
Posts: n/a
Default Excel ignores boot-time regional settings when interpreting a date

I have a third party DDE app that exports dates as strings, correctly
using the short date format in the regional and language settings,
which, in my case is MM/DD/YYYY (reset at every boot).

Unfortunately excel insists in interpreting that date as DD/MM/YYYY
[Application.International(xlDateOrder)=1, it should be 0],
disregarding my regional settings. The result is that all the dates are
wrong or #VALUES!

If this is not wierd enough, listen to this: it only happens after I
reboot (when the date format is reset to MM/DD/YYYY). If subsequently I
change the short date format in the regional settings to something
different and then back to MM/DD/YYYY (exactly what it was when I
booted) and restart excel, it now uses the correct date format. So it
looks like excel is not reading correctly the regional settings at boot
time.

Since I need to distribute the spreadsheet (with the DDE link), I
cannot make assumption about the regional settings of the clients. The
third party application does not give any problem, if excel worked as
expected the two should be in sync as far as date formatting goes...
I'd appreciate some insight.

To replicate the problem, create a spreadsheet with a macro

public sub checkDateFmt()
select case Application.International(xlDateOrder)
case 0
msgbox "MMDD"
case 1
msgbox "DDMM"
case else
msgbox "Other"
end select
end sub

Run the macro and check against your regional settings.

  #2   Report Post  
 
Posts: n/a
Default Excel ignores boot-time regional settings when interpreting a date

In fact I was not correct in stating that it does not work at any new
log-in.... It would be too simple... This is the rule:

My system short date format is reset to be "MM/dd/yyyy", Regional
Options = English (United Kingdom).

If before logging out I change the date to a US format ("MM/dd/yyyy" or
"m-d-yy"), when I login again both the regional settings and excel are
correctly set to MM/dd/yyyy.

If before logging out I change the date to a non US format
("dd/mm/yyyy" or "d-mmm-yy") when I login again the regional settings
read MM/dd/yyyy but excel uses DD/MM/yyyy.

It looks like excel does not like the fact that in our profile we use
UK language with US dates.

  #3   Report Post  
 
Posts: n/a
Default Excel ignores boot-time regional settings when interpreting a date

Solved!

The situation was due to a login script that was changing

HKEY_CURRENT_USER\Control Panel\International\sShortDate

BUT when the sShortDate is modified also other "secondary" keys (not
accessible via the Regional & Language Options dialog) need to be
changed accordingly. When you press "Apply" in the Regional & Language
Options dialog, these "secondary" keys are modified automatically. But
if you change the sShortDate key directly via code, you need to make
sure you also change the "secondary" keys.

In particular the key

HKEY_CURRENT_USER\Control Panel\International\iDate

must be set to 0 for MMDD or 1 for DDMM. This is the key used by excel
to interpret a text date. In my case sShorDate and iDate were not in
sync.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Time Manipulation BFiedler Excel Discussion (Misc queries) 0 September 15th 05 01:15 AM
convert interval to various separate date , time, hr, minutes Todd F. Excel Worksheet Functions 4 July 12th 05 07:25 PM
Imported Date & Time format with calcs. managed in excel from imrp Todd F. Excel Worksheet Functions 0 July 8th 05 09:03 PM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 01:07 AM
Combined date time cell to separate date & time components Mark Ada Excel Discussion (Misc queries) 1 December 2nd 04 01:07 AM


All times are GMT +1. The time now is 12:12 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"