Date Format Mismatch in Excel and Word file(Mail Merge)



I have some data in EXCEL file.
I want to use that data in Word file with Mail merge.
In excel ,Sanction Date column Date is DD-MMM-YYYY format (i.e 03-Sep-2015).
But when i take this data in word file with Mail merge date appears as 09-03-2015.
I want data in Word File as-03-Sep-2015 or 03-09-2015.

Please assist.

Thank You



Selected Answer

To solve this problem you need to understand dates in MS Office, especially Excel. Today is the 43737th day since Jan 1, 1900. It follows that tomorrow will be the 43738th day and yesterday was the 43736th. Excel simply assigns a number to each date. This enables calculations: TODAY()+2 = 43739 or Monday, October 1, 2019.

Therefore, if you write the formula =TODAY()+2 in a cell that cell will be given the Value of 43739. But you will probably see 10-1-2019. This is because Excel will see that the formula returns a date and format the cell automatically to display that date. It will assign the standard Short Date format which is set in the Control Panel > Clock, Language, Region > Change Date, Time or Number Formats for Windows. Excel, Word etc. take it from there.

Excel applied the Date format to your experimental cell because the format of that cell was set to General which causes Excel to guess at your intention. At Home > Format > Format Cells > Numbers > Date (or Custom) you can set a different format. For example, if you set the format to dddd your experimental cell will display Monday while the value remains 43739 = TODAY()+2. Of course, TODAY()+2 won't be October 1 forever. It will always specify the day after tomorrow.

Now to your problem. In your Excel workbook you seem to have a true date, meaning a number. Make the number visible by formatting a cell containing a date as "Number". Your Excel worksheet displays the date the way you want because the cell is formatted as dd-mmm-yyyy. When you transfer the worksheet to Word, however, the cell's Value is transferred, recognised by Word as a date and then converted (by Word) to a date applying the default Short Date format. That format seems to be mm-dd-yyyy. There are two solutions to the problem.

  1. Change the Short Date format in the Control Panel. You might make it dd-mmm-yyyy but that would come back to haunt you in other circumstances because, in Windows talk, that isn't a Short Date.
  2. Convert the dates in your Excel source file to text strings before exporting to Word. For this you need a helper column and two steps.
    1. Presuming that your dates are in column A, enter this formula in the helper column. =TEXT(A1,"dd-mmm-yyyy"). Copy down.
    2. Select all formulas in the helper column and press Copy or Ctl+C.
      Without changing the selection, press Paste > Paste Special > Values (from the ribbon's Home tab). This will change all formulas to the values they produced, effectively dates in string format which Word will not change when received in transfer.


It worked.
Thank you Variatus.

KDRAGHU (rep: 10) Sep 29, '19 at 11:59 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login