Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am working on a global financial app that uses Excel for the UI. The dates should be displayed in the appropriate format for each language & culture. For some columns I am able to change the date format but not for others. How can I change Excel 2010 date format display? Date Format is not changed

share|improve this question

migrated from Dec 10 '12 at 20:24

This question came from our site for professional and enthusiast programmers.

As the (*) in the screen-cap says, * formats are dependent on system settings, e.g. what you set in the Windows regional settings control panel. – Marc B Dec 10 '12 at 17:03
up vote 2 down vote accepted

If you can't change the format for some dates that may mean those dates are formatted as text.

Test by using ISNUMBER function, e.g. =ISNUMBER(A1) - FALSE indicates those dates are formatted as text.

If that's the case you can convert a whole column to true dates by using Text to Columns functionality.

Select a single column > Data > Text to columns > Next > Next > under "column data format" choose "Date" and from the dropdown choose the appropriate format based on the source, e.g. if dates look like 2012-12-25 then that's YMD > OK > ESC

Now format dates using one of the * formats so that they change according to regional settings as Marc B says

share|improve this answer
Yep, changing the "column data format" resolved the issue. Thanks, Barry. – Tarzan Dec 10 '12 at 19:39

Use the optional Local:=True when opening the file programmatically. This ensures that any number stored as a serial date will be formatted per the user's system locale.

Sub OpenFileLocalDateSettings()
Dim wbFullName as String
Dim wb as Workbook
wbFullName = "C:\The File You Want To Open.xlsx"
Set wb = Workbooks.Open(Filename:=wbFullName, Local:=True)
' execute more code below this line
End SUb
share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .