Importing Excel into InDesign - a date problem

0800 156 0777

Importing Excel into InDesign - a date problem

Some time back I was training an advanced InDesign course, and one of trainees had come across a problem and asked me for advice. The problem she had come across was, whenever she imported Excel documents into InDesign, the dates switched formats.

What was happening was that the date was formatted within Excel as an english date format (say 1/12/14) but after importing into InDesign it swapped into an American date format (12/1/14). And she could not figure out why.

After a few minutes of investigation I finally figured out why. Here is a screenshot of a simple Excel table. Just a column of dates, formatted (you might think) in the English date format.

Simple Excel Table
Simple Excel table

Here is what happens when you place it into InDesign (or copy/paste)…

 

 

Placing an excel document into InDesign

Placing an excel document into InDesign

As you can see the dates have clearly been swapped around.

At first I thought it was InDesign, some setting or preference - but there are no settings or preferences that deal with dates. So the next thing I looked at was the actual Excel document, and if you have ever dealt with anything within Excel, you now that dates and formulae are formatted - so I checked the formatting that was applied. Bingo!

Excel has various date formats that could be used, and although some look like an English format, some have an asterisk before them…

*1/12/11

So, on the face of it everything was ok. But this was the default setting, and why put an asterisk in front? This, looking at the explanation below the formatting within Excel, was so that the date was formatted according to the computer's country setting.  What? Why would this ever be the default setting? I have no idea, but obviously what was happening was the document formatted it as English, but the computer's country setting was American.

So, changing the date setting to an English format (without the *) fixed the problem. Here is a screenshot of the formatting that was applied to the cells within Excel…

Placing for the second time

Excel formatting screenshot

Now I just changed the formatting to an English format (no asterisk in front) - job done. Placing the Excel document again produced the right result…

Simple Excel Table

Placing for the second time

The moral of the story is, always check your styles and any formatting that has been applied, both in the source document and in the final document.