Excel 2007 Writer : keep original date format

Mar 19, 2009 at 10:55 AM

I'm reading an Excel 2007 file, modify some values and then save it to a new file.

I have a column full of dates in the dd/mm/yyyy format (17/03/2009) and write in the next column dates in the same format.

Once I'm done, I open my previously saved file with Excel 2007.
My problem is that all the dates are now in the mm-dd-yyyy (03-17-2009) format. Even the ones I didn't write.

How do I keep the original format of the dates (dd/mm/yyyy) ?
Mar 19, 2009 at 4:35 PM
The problem is most likely that you are using one of Excel's built-in locale dependent date number formats. These are the default choice by MS Office Excel whenever you type a date in a cell.

Check whether your cells are formatted using a built-in locale dependent date number format like this:
  1. Open your original file in MS Office Excel.
  2. Click on the relevant cell holding your date.
  3. Open the number format dialog pane in the style dialog by clicking on the arrow shortcut in the number format section
  4. You should now see high-lighted the current date number format for the cell.
  5. Look whether the date number format has an asterisk indicating locale dependent format.

Sample screen shot of step 5.

When you use one of the locale dependent date number formats the date will be formatted according to the preferences set in the operating system (regional settings). Your Excel file will not look the same on different platforms!

Sometimes this is wanted behavior for your Excel workbook, sometimes not. In your case, it sounds like "not", and you should instead select one of the date number formats without an asterisk, or type a user-defined format.

PHPExcel does not have real support for built-in locale dependent date number formats. When you read an Excel file containing such formats, it converts the date to a "hard" generic date number format. This explains the behavior where date formats are converted.

In short, your solution for now is to use a "hard-coded" date number format (one without an asterisk), or type your own as a user-defined one.

Mar 20, 2009 at 9:01 AM
Edited Mar 20, 2009 at 11:26 AM
Thank you for your extended answer, I am in fact using a specific format (French locale dd/mm/yyyy) with an asterisk in front of it.

But as far as I understand I should keep the cell's format of the file (date locale). Because I open the Excel file on a system with a french locale, then modify on my web server, download it open it back on my local system (It seems to me that PHPExcel is changing the cell's format)
What happens instead is that the cell's format switches from the dd/mm/yyyy to mm-dd-yyyy. (see screenshots below).

I'm able to manually force the date format for each cell, it's ok but not perfect because I loose the year on 4 positions and have to do the work on each columns :


Why do I lose the format ?
Could I set the cell's format to a simple string (not a date format) in worst case ?
Mar 21, 2009 at 12:47 PM
Here are some experiments / tests:

1. First I try to read and write an xlsx file where date is formatted using built-in locale dependent date number format.


Like you noted, date format is being converted.
'20/03/2009' changes to '03-20-2009'

2. Let's try to correct this on fly by applying a custom number format:



Now the date appears as '20-03-2009'. Apparently, slashes cannot be trusted to render as slashes. Not sure why.
Also, strangely, when I open the style dialog, it does not open up as a user-defined number format even if it is.

3. Let's make the number format more explicit:



Now the date appears as '20/03/2009' which is what we want. Escaping the slashes tells Excel that the slashes are literal.

4. Finally, let's go back and modifiy the date number format of the original xlsx file to a user-defined one.


The "before" screen shot shows how we type a custom date number format. Since this is in a Danish version of Excel, we type "dd/mm/åååå" (without the quotation marks). Excel understands that the slashes are literal.

Read/write works ok. No modification needed.


There is still work to be done in these classes documenting and investigating how (date) number formats work. Dates and number formats are confusing in Excel and it is hard to find detailed information about the subject.

In your case, example 3 and 4 may what is needed to solve the problems.
Mar 23, 2009 at 11:04 AM
Ok thanks a lot,
I didn't know you could define a date format different from the class' contants.
I'm using the solution to force the date format of each cells to 'dd\\/mm\\/yyyy'.