Excel 2010 – Fixing Mail Merge Data – When Dates Show Up as Numbers

“The dates from my mail merge from Excel into Word are showing up as numbers instead of dates – how do I fix this?”

Issue: Recently, we had someone write to us because he was having trouble getting his dates to format correctly in his mail merge document. Some of his dates, derived from an Excel spreadsheet, were appearing as numbers in his Word document:

Solution:  While we were not able to look directly at the file, I came to the conclusion that the problem did not lie within the Word mail merge document, but with the formatting in the Excel spreadsheet itself. What probably happened was that some of the dates were actually formatted as numbers instead of dates.

Here’s a bit of background to put this into context:

Any date you enter into an Excel spreadsheet actually gets stored behind the scenes as a numbers. This is called the serial date. The number represents how many days have passed between January 0, 1900 – yes, I did say January 0, 1900 – and the date you’ve input. One thing to note – the number 1 represents January 1, 1900 BUT the number 0 does not represent January 31, 1899. So, now that we know all of this, how can we use this information to fix the problem?

Well, the solution is fairly simple. All we have to do is reformat the spreadsheet so that our dates are formatted as such. Here’s how:

1. Clear the formatting from the cells you are working with. To do this, select the cells you are changing. Then, in the Editing group on the Home tab, click on the drop down arrow beside the Clear command (white eraser icon):

2. Select Clear Formats. Now, take a look in at what’s happened to your formatting. All of your selected data will now appear as numbers. Further to that, if you look in the Number group on the Home tab, you’ll see that all of your data has now switched to “General” formatting:

3. All that’s left to do is format your data as dates. In the Number group on the Home tab, click on the drop down arrow next to where it says “General”. This will give you a list of several formatting options available. If you want to see more options, you can either click on More Number Formats or you can get out of the drop down menu and then click on the Dialogue Box Launcher. That’s the little box in the right-hand corner with an arrow pointing South-East. In this case, however, all you need to do is select either date option (Short Date or Long Date). Unless you want your date to appear a certain way in your Excel spreadsheet, it really doesn’t matter how it looks as long as it’s formatted specifically as a date:

4. You’re now ready to use in your Word mail merge document! Your dates should now appear as you want them to:

-Katie Caplan
Client Service Coordinator,
Sector Learning Solutions