Skip to main content

Setting Per-Employee Reference Dates in a Mail Merge Spreadsheet (Pyn 2)

Give each employee their own reference date when manually enrolling from a spreadsheet by adding a "Reference Date" column.

Written by Jon Williams

The instructions below are for Pyn 2. Per-employee reference dates are not available in Pyn 1 (Classic).

When you manually enroll employees in a flow from a spreadsheet, every employee normally gets the same Reference Date, the one you set in the enrollment dialog. If your list mixes people with different dates (for example, several new hires with different start dates), you can now give each row its own date by adding a Reference Date column to your spreadsheet.

Rows with a date use that date. Rows with a blank cell use the Reference Date from the dialog. Spreadsheets without the column work exactly as before.

What You Need

  • A .csv, .xls or .xlsx file with a header row.

  • An Email column containing each employee's primary work email.

  • A Reference Date column with dates written as yyyy-MM-dd, for example 2026-09-21.

  • Optionally, any other columns you like. These become custom tokens you can use in the flow's messages (see below).

Step-by-Step: Enroll With Per-Employee Dates

  1. Build your spreadsheet. Add a header row with Email and Reference Date columns, plus any extra columns you want as tokens.

  2. Fill in the dates. Type each date as yyyy-MM-dd. In Excel or Google Sheets a real date-formatted cell also works, however it is displayed. Leave the cell blank for anyone who should use the date from the dialog.

  3. Open the flow and start a manual enrollment. Set the Reference Date in the dialog. This is the fallback date for every row that has no date of its own.

  4. Choose "Upload mail merge spreadsheet" and select your file. (The plain Upload a CSV, XLS, or XLSX file option also recognises a header row with extra columns and treats it as a mail merge.)

  5. Check the notices under the upload. You will see a message confirming that reference dates were taken from the spreadsheet's "Reference Date" column, plus a list of any custom token columns. If any rows were reported as having an invalid date, fix them and upload those rows again.

  6. Click Next, review the employee list and click Confirm. Each employee is enrolled against their own reference date.

Naming the Column

Pyn finds the column by its header, and capitalisation does not matter. Any of these work:

  • Reference Date, reference date, REFERENCE DATE

  • Reference_Date, ReferenceDate

  • Ref Date, ref-date

Headers such as Start Date or Reference Date (optional) are not recognised. They are treated as an ordinary custom token column instead. If two columns both match, the first one is used.

Accepted Date Formats

  • yyyy-MM-dd (recommended), e.g. 2026-09-21

  • yyyy/MM/dd, e.g. 2026/09/21

  • A real date cell in an .xls or .xlsx file, whatever display format the cell uses

  • A full date and time such as 2026-09-21T00:00:00 (only the date part is used)

Not accepted: 14/09/2026, 09/14/2026, 14-09-2026, 21 Sep 2026, or impossible dates such as 2026-02-30. Day-first and month-first values are rejected on purpose: they mean different things to US and non-US readers, so Pyn will not guess.

💡 Tip: If your sheet is already in dd/mm/yyyy format, format the column as a real date in Excel or Google Sheets and upload it as .xlsx, or change the column's number format to yyyy-mm-dd before exporting to CSV.

How Pyn Handles Each Row

  • Blank cell: the employee gets the Reference Date you set in the dialog. If you upload a second file in the same dialog, a blank cell also clears any date that employee received from the earlier file.

  • Valid date: overrides the dialog date for that employee only. If you remove the employee from the selection, their date is removed too.

  • Invalid or ambiguous date: that row is not enrolled. Pyn shows a notice that the row has a reference date that is not a valid yyyy-MM-dd date, with a list of the affected emails and values you can copy. The other rows in the file are still added, so you only need to fix and re-upload the flagged ones.

  • Other columns still become custom tokens: every extra column is available in your messages as custom.<columnName>, for example a Start Location column becomes custom.startLocation. The Reference Date column itself is never a token.

  • Email and Reference Date only is fine: a two-column spreadsheet with no other columns is accepted as a mail merge.

  • Everything else is unchanged: Verify Mode, Flag as Test, the 1,000-row limit, duplicate handling (the first row for an email wins) and matching by primary work email all behave as they do today.

⚠️ Things to Keep in Mind

  • Dates must already be in an accepted format. Pyn does not convert day-first or month-first text. In a CSV file the cell text itself must read yyyy-MM-dd.

  • The confirmation screen lists the employees but does not show each person's reference date. Check your spreadsheet and the notice under the upload before you click Confirm.

  • Only the first sheet of an .xls or .xlsx workbook is read.

  • Reference dates are calendar dates with no time of day or time zone. A date-and-time value only contributes its date.

  • Per-row dates apply to spreadsheet uploads only. Employees added by searching or by pasting email addresses always use the Reference Date from the dialog.

Did this answer your question?