I have a merge variable {{Suspense Date}} along with other variable column headings in a CSV spreadsheet (originally an Excel document saved as .csv so I can use it in Thunderbird Mail-Merge). As of a few months ago, the date no longer shows as a date but as a conversion to text. The format in the Excel (and .csv) is Date (specifically *3/14/2012 format) not Text or General. Yet when I run mail merge with the .csv file, everything but the {{Suspense Date}} variable merges fine. Suspense date should show 5/01/2020 but it instead inserts 43952.
13 Answers
I use Thunderbird (in Windows 10) and have a similar problem when I use an Excel Spreadsheet as the data source for the Mail Merge.
As an example, the date 14-Apr-94 shown in the Spreadsheet ended up displayed as a figure 43535 in the Merged Email Document when I used the spreadsheet itself as the source.
If I saved the file with Excel as a *.CSV file, the date field, although saved as a text field, displayed in the *. CSV file as 14-Apr-94. The 14-Apr-94 date then displayed correctly in the Merged Email Document.
As indicated by Sebastian you need to check how Excel is saving the data in the CSV file, and make sure that CSV has exactly the text you want displayed.
0.CSV saves all fields with the Text format so if you save it as Date in Excel, it will be converted into numbers when it is converted into Text. You can test this by selecting the field with Date type in Excel (make sure it has dates) and converting the type to Text. It will all be converted into numbers.
These numbers are created so that 01/01/1900 is 1 and every day the number increases by 1. So the value of 05/01/2020 is 43952. I know. It's a pain in the...
Well anyway, to solve this, you have to make sure that the field that contains your dates actually have the Text format. You will obviously have to manually type up the dates which is also annoying.
But there is a workaround. You can,
- Type your dates in a field with the Date format.
- Select all the dates and Copy-Paste them into a .txt file.
- Copy them again and paste them in a field with the Text format.
I found a workaround by Gouray Kumar. There is no need to save a CSV file for a Mail Merge just use your excel spreadsheet for the Mail Merge with a TEXT Function to generate the required format.
See Example with four fields in the spreadsheet Column One formatted as a date and, Column 2 is formatted as a number with 2 decimal places.
Name: UDATE UPAID FIRSTNAME SURNAME
Data 12-Mar-78 34.90 John Smith
The Mail Merge email displays the Date Field as 36156 and Paid Field as 34.9. The FIRSTNAME and SURNAME display correctly.
With the workaround add a Column E and Column F to the spreadsheet with New Names and use the TEXT Function to paste the values of Column A and Column B into Column E and Column F.
Name: DATE PAID =TEXT(A2,"dd-mmm-yy") =TEXT(B2,"#.00")
Insert the DATE and PAID names in the Mail Merge Letter instead of UDATE and UPAID and the Date and Numbers Fields display correctly.
The process is not as onerous as it looks, as once the additional fields with the TEXT Function are set up the formulae can be copied down the column, any changes in Column A and Column B are automatically updated to Column E and F, it should only be necessary to create additional columns for any Date or Numeric Fields in a spreadsheet as the other fields seem to display correctly. The above also works with Libre Office Calc Spreadsheets.
