Excel Date Formatting VS Regional Settings

[asvforce]

I have a problem where I am creating excel file with date format of "mm/dd/yyyy" and my machine's regional settings are English (US). Now when my end user downloads my excel (who has English (Canada)) they see the excel file with no issues except the date's messed up due to their regional settings. So, my date is "08/05/2011" and when they open the excel file they see "5/8/2011". But what i noticed is when they try to edit the cell they still see original value "08/05/2011" and hence they start complaining that the dates info are incorrect.

My question here is that Is there anyway where they dont have to change their regional settings but still see the correct date formats in original as (mm/dd/yyyy)?

Please suggest, Thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

A date in Excel is just a number. For example today, 31 Aug 2011, is 40786. Formatting only affects how it is displayed, not the actual value.

 

[VoG]

They can try this: select the dates, Data > Text to columns, click Next twice, check Date and select MDY then click Finish. That should adjust them so that they display as dd/mm/yyyy (or whatever their regional setting is).

 

[Richard Schollar]

If this is an Excel file and the format has explicitly been set (eg as mm/dd/yyyy via the Format>Cells menu (in xl2003 or below), somewhere else on the ribbon in 2007 and above) then the display shouldn't change, irrespective of the regional settings.

 

[asvforce]

Thanks for the replies.

Actually i am creating the excel files in Excel 2003 and then I am applying the column formats using VBA.

So, i guess the moral would be they will have to do something on their end to get it in my original format.

And there is nothing which i can do so that it overrides the Regional settings of the end user. Correct?

 

[Richard Schollar]

What format are you saving the file in and how are you applying the number format? An example of the code used will help.

 

[asvforce]

I save it as Excel 97-2003 workbook and the number format is set as 'm/d/yyyy'.

 

[Richard Schollar]

Can you provide the code? If you are explicitly setting the number format of the date in code, I wouldn't have thought the regional settings should have any impact on the display (the display should follow whatever explicit format you are setting)

 

[asvforce]

The code is in VB file and is dynamic so its bit hard to put it all here BUT I set my column format depending on the setting for a column in my master control table. So lets say if the col1 is set to use FormatType value "mm/dd/yyyy" then i use below code to apply the setting.

Selection.NumberFormat = """ & FormatType & """"

 

[Richard Schollar]

I'm suprised - I would have thougt what you have would prevent the display changing based on regional settings. I will have to test this and see if the same thing happens on my system...