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.

0
11/2/2005 3:24:22 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
298 Views

Similar Articles

[PageSpeed] 47

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.

0
11/2/2005 5:03:27 PM
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.

0
11/4/2005 11:44:42 AM
Reply:

Similar Artilces:

Pasting data from Excel
Hello everyone, I'm not sure if I should be posting this question here or in the Excel forum but here goes. Is it possible to copy data from multiple cells in Excel and then paste them into multiple lines of the criteria section of an Access query? For example, Given cells and values: A1- 1 A2- 2 A3- 3 I would like to be able to copy this data from Excel and paste it into an Access query like : Criteria: 1 or: 2 3 I am using Access 2002 SP3 and Exc...

Excel Drop Down Box
I'm trying to edit an excel worksheet that has drop down boxes. However, the drop down boxes are not typical forms. These drop dow boxes appear to be normal cells (They contain text). When I click o the box, a little gray box shows up w/ a down arrow to the right of th cell. However, if you right click on the cell, there aren't an property options that are displayed. I was wondering if anybody had any idea what kind of drop down box thi is. How can I edit or create one -- Message posted from http://www.ExcelForum.com It sounds like it's under Data|Validation. chris313 wr...

Add SafePay footer record for date and account number
Union Bank of California has a Positive Pay format that requests a footer record for each day and account number. So if you transmit checks issued on two dates for a single account, the SafePay file would have two footer records--one for each date. Currently, I am only able to create a footer by account, totalling all checks issued for that account (regardless of date), and attributing that total to the Issue Date in the footer record. Union Bank reads the issue date on the footer, and sees that the checks issued on that date do not match the footer total, causing them to consider th...

How to use outlook address in Excel
Hello, I have an Excel sheet which I use as an invoicing-application. I would like to retrieve address-data from Outlook where I keep all my contact-data of my customers. So, I want to select a customer from my Outlook contactlist when I am writing a new invoice in Excel. In Word, I have a macro which does this, but unfortunately the Application.GetAddress does not work in Excel. Can somebody help me ? "Henny Slokker" wrote: > Hello, > > I have an Excel sheet which I use as an invoicing-application. I would like > to retrieve address-data from Outlook where I...

how to select multiple text boxes in excel for formatting
I am trying to select multiple text boxes for formatting the font but seem unable to select all of them other than to click on each one individually. Is there an easy way to select all of the text boxes at once? To select multiple objects on the sheet -- Click on one object Hold the Ctrl key, and click on additional objects To select all the objects on the sheet -- Choose Edit>Go To, click Special Select Objects, click OK Or, to work with specific objects, you can add the 'Select Multiple Objects' tool to one of your toolbars: Choose Tools>Customize Select the Commands tab...

excel margin issues on landscape
When I print a spreadsheet I cant get it to print to the full page - it prints smaller unlike older excel program. Also when i set the margins for a spreadsheet the left hand margin wont move over to the edge of page like right hand side? In Page Setup: If you are using the Scaling option to print to a certain number of pages wide by pages tall and/or you are using the columns to repeat at left, try: - clearing the number of pages tall value (so that it is blank), and/or - if you are printing to one page wide, remove the columns to repeat at left Simon "Peter MB" wrote: >...

Excel macro list
In Excel 2003 I used to be able to list all macros in a workbook by pressing Alt+F8. Now all I get is a series of ribbon help letters... What's changed? Is there still a way of accessing macros via Alt+F8? Any suggestions appreciated. Hi, ALT+F8 works for me in E2007. What do you mean by 'I get is a series of ribbon help letters' Mike "pbaker6" wrote: > In Excel 2003 I used to be able to list all macros in a workbook by pressing > Alt+F8. Now all I get is a series of ribbon help letters... What's changed? > Is there still a way of...

Can I only allow printing to pdf in Excel?
I have created a template in Excel which has been set up so that the layout is perfect when printing to pdf (which is how the document will mostly be used) but the layout changes if printing direct to our printer. Is there a way that I can ONLY allow printing to pdf from this document? Hello You may consider using some VBA to achieve this. One way is to use the Workbook_BeforePrint event and specify the pdf printer in the PrintOut method, eg: Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PrintOut copies:=1, ActivePrinter:="CutePDF Writer on CPW2:" End Sub Pl...

Set field focus in a subroutine
In Access 2003 (Windows XP) I am passing the value of a field in a textbox on a form to a subroutine to validate that the date value is within a range. If the date is out of range I would like to set the focus of the field on the form and display an error message. When I pass in the field to the subroutine, I get a compile error "Invalid qualifier" when I try to set focus to the date field. How can I set the focus to the field within the subroutine. Here is the subrotuine code: Public Sub CheckDates(date1 As Date) If Not IsNull(date1) And date1 < [Forms]![frmMR...

Problem in Setting up CRM server
Hi We are trying to install CRM server. but we getting following error message and we don't know the reason for this. Action Microsoft.Crm.Setup.Server.GrantDatabaseAccessAction failed. ---> Microsoft.Crm.Setup.Common.SetupException: Setup could not complete this action. This might be due to the fact that there are multiple Domain Controllers and they have not replicated new Microsoft CRM information yet. If this is the case you have several options: 1. Go to the domain controller and manually force synchronization. 2. Wait 15 minutes (typically) until the domain controller synchron...

query will not write to excel
I have set up a query to a Foxpro .dbf file in a database from excel. When I tell Excel to import the data it it appears to be working but never seems to return the data. Running the same query via msquery.exe returns the data with no problem. Can anyone tell me what the problem is ? ...

VB: using a string to set a range object?
I'm a bit new to the excel "range" object type. I was suprised to see that while I can do: dim chunk as Range chunk = .Range(A5:B6) I apparently cannot do: dim chunk as Range dim stuff as string string = "A5:B6" chunk= .Range(string) How can I concatenate up a string describing a range, and then use it to define a range object's target cells? - Ross. Oops, I meant chunk = .Range("A5:B6") in the first example - I forgot the quotes. R. "RGK" <nothanks@nospam.go> wrote in message news:RqydnSWzbu_OEZbeRVn-2A@...

Outlook 2003: Run-time error / Automation Error
We have been using a 3rd party add-in for several years with various configurations of Outlook/Windows and Exchange 5.5. The application is called organiziQ.Team and we are no longer eligible for support (nor does the company support the product anymore to my knowledge). It allows the user to focus on an individual calendar while seeing an overview of multiple calendars at the same time. Anyway, I'm trying out Office 2003 (we have it anyway through our organization) and I can't seem to get the application to launch properly. The system is fully patched. I'll add the add-in, the...

EXCEL FORMULA #28
Good afternoon, I'm trying to fine a formula which would show me how much money I would save on a mortgage if I were to pay additional principal each month--in addition to paying the additional principal how long would it take to pay off. I'm looking at a 160k mortgage at 7.5 for 30 years. I'll like to pay this off as soon as possible by paying additional principal each month. There are tons of free templates at: http://office.microsoft.com/en-us/default.aspx Maybe you'll find something you like. Kam1999i wrote: > > Good afternoon, > > I'm ...

OLE: Excel.Application
Hello, in VB.Net, I use Excel to display results : dim xl as new Excel.Application // creates an Excel process // snip (putting values into cells) xl.Visible = true If the user closes the Excel file and then my program, the Excel process is killed in memory, which is good. If the user closes my program first and then the Excel file, the Excel process remains in memory ! How can I make sure the process will be killed ? Thanks ! Hi You need to set xl.quit (and before that ensure that excel doesn't halt and ask things like "save changes?" on quitting) somewhere in your p...

Export relationship information from Visio to Excel
Hello all, Is there a way to export information regarding relationships from a visio diagram to an excel spreadsheet? In addition, is there a way to tell the excel spreadsheet to eliminate or change a relationship and for that action to be applied onto the visio diagram? thanks, ivan as a general answer I'd have to say "no, not without custom code". You didn't define what you meant as a relationship. al "Ivan Salas" <IvanSalas@discussions.microsoft.com> wrote in message news:6332A594-E4AF-4E8B-BA2D-7A4BC17962B3@microsoft.com... > Hello all, > &...

startup excel euro symbol
when i digit € symbol inside any application excel 2007 automacic startup and for me is impossible to use this symbol anywhere, i use windows xp professional ..have you a response to solve this problem? thanks ...

Excel 2003 Print Issue
I have created a spreadsheet to help with a university engineering assignment and I have added a worksheet that is basically an automatically generated report of all the calculations. I have set the Print Area up in such a way so that the results are printed out in well defined pages (e.g. page 1: title page, page 2: summary of input variables, page 3: summary of calculation results etc). The report is arranged vertically in the worksheet, so the pages are 'stacked' on top of each other. It prints out fine in Excel 2000 and 2002 but I recently upgraded to Excel 2003 and now find tha...

Formula for Date
I'm new to formulas and just want to display the current date in my outlook form (e.g., December 18, 2004). What I've done is created a combination text field where I have the following fields: [Email Opening Date] [Full Name] [Job Title] [Company] [Business Address] Dear [Full Name]: When I send a new message, I then copy the values into my email instead of copying the data (name, title, address, salutation) one field at a time. This allows me to personalize the email. The problem is that I do not know what to do to with formulas to show the current date as I note above. Thank...

how do I set up my email with outlook
I have just installed outlook but I can send emails form outlook, what do I need to set up and to link to yahoo? tokyo gyoza wrote: > I have just installed outlook but I can send emails form outlook, > what do I need to set up and to link to yahoo? You have to pay Yahoo if you want to use it in Outlook (via POP). ...

Creat a time book
I'm building a semi automated time book in Access. what i want is to be able to give access a two week period prefferably by specifying the beginning and end dates and have access add an entry to a table i'm going to call the 'Time Book' for each person in a personnell table for each day. the best i have been able to come up with is to pack a Macro with 14 queries, each adds one more day to a specified starting point. one of the problems i'm running into is that some of the shifts run over night and Access doesn't calculate the shift end correctly. I wo...

excel 2000 message
excel 2000 message - 'cannot use object linking and embedding' Were they hit by the MSBlast worm? One poster (Lutz Meyer) guessed that this was the cause of his problems. I haven't seen any confirmation/denial, but you may want to read his post: http://groups.google.com/groups?threadm=3F3971AF.FA4490F5%40msn.com Post back with your results. I'm curious if that was the problem. (It's come up quite a few times since MSBlast hit.) bill bootle wrote: > > excel 2000 message - 'cannot use object linking and > embedding' -- Dave Peterson ec35720@msn.c...

Excel Graphing Line References off when chart is a sheet.
I have noticed that when any graph is created in EXCEL and you hover you mouse over the dataline you receive that corect response. If you convert the chart to a sheet, the hover of the data line is now not representative of the the y axis directly below it. The data being graphed is correct now the hover represents the "series" (x-Axis) correctly but does not represent the "Point" (y-axis) correctly at all. Tne Y-axis datapoint reference is wrong. Any help? ...

Smartlist MDA lookup is missing the GL Posting Date.
There is two different dates in the MDA tables, one is for Posting Date and the other is the Transaction date. The DTA10100 table has a TRXDATE field, which is actually the GL Posting Date, and the DTA10200 table contains the same field name, TRXDATE, which is the Transaction Date from the sub-module. In Smart List, it only allows us to look at the Transaction Date from the DTA10200 and the customers want to look up the MDA records by the GL Posting Date, which Smart List doesn't allow. We need to add the Date from the DTA10100 table as the GL Posting Date. ---------------- This p...

Editable Excel Spreadsheet Online?
Hi, I tried to recent find information on this, but could find very little. How difficult would it be to host an excel spreadsheet online where visitors to the site can directly view and edit it? Right now, I can upload the spreadsheet to our web site and visitors can view it, but if they edit it, they can only save it to their local drive. I would like the users to be able to save the copy on the server. What would be involved in something like this? I assume for starters (if it's do-able) we'd need Windows hosting (we're not hosting ourselves) and some ASP support. Any de...