Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 stackoverflow.com Dec 10 '12 at 20:24

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

1  
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
add comment

2 Answers

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
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.