Template Issues

How to Use a Mail Merge Date Prompt to Select Matching Excel Records

Muhammad TalhaMuhammad Talha Nov 2, 2026 868 views

Question details

The user wants to use a date prompt during a Mail Merge to dynamically filter and generate letters only for Excel records that match the entered date.

How to Use a Mail Merge Date Prompt to Filter Matching Excel Records
Product
Word
Device & OS
not provided
Scenario
Generating specific letters from a large Excel dataset based on an enquiry date without needing to hard-code the date for every merge.
Observed behavior
The user needs to know how to set up a FILLIN field so the date is entered once and properly matches the mm/dd/yyyy format of the Excel date fields.
Before you start

Verify that your Excel data source has a dedicated date column (such as 'Date_of_Enquiry') and that the dates are consistently formatted as mm/dd/yyyy.

Solution 1Recommended

Use a FILLIN Field to Filter Merge Records

Insert a FILLIN field to prompt for the desired date once, and use it in conjunction with your merge rules to filter the Excel dataset.

By configuring a FILLIN field, Word will pause the merge process to ask you for a date. You can apply this prompted date to your document and comparison rules to ensure only the matching records print out.

1
Connect the Excel data source

Open your Word document, go to the 'Mailings' tab, click 'Select Recipients', and choose 'Use an Existing List' to locate and open your Excel spreadsheet.

2
Insert the FILLIN prompt

Navigate to the 'Mailings' tab, click 'Rules', and select 'Fill-in'. In the Prompt box, type 'Date of Enquiry' and check the box for 'Ask once' if you want the prompt to apply to the entire merge rather than per record. Click 'OK'.

3
Enter the date format

When the prompt dialog appears, enter the required date exactly matching the format used in your Excel file (e.g., mm/dd/yyyy). This ensures Word can correctly compare the text.

4
Set up the comparison filter

To skip records that do not match, you can use the 'Skip Record If' rule under the 'Rules' menu. Select your 'Date_of_Enquiry' merge field, choose 'Not Equal to', and compare it against your prompted date value.

5
Execute the Mail Merge

Once your fields and rules are set, click 'Finish & Merge' on the Mailings tab. Select 'Edit Individual Documents' to run the merge and generate letters exclusively for the matching dates.

Use a FILLIN Field to Filter Merge Records
Format Matching: Ensure the date you type into the prompt exactly matches the format in your Excel spreadsheet (such as 05/12/2024 instead of May 12, 2024) to prevent comparison errors.
Efficient Mail Merge Setup

Perform Advanced Mail Merges Effortlessly with WPS Writer

WPS Office offers a robust and intuitive Mail Merge feature. You can seamlessly link Excel spreadsheets, insert prompt rules, and generate customized batch letters quickly, all within a clean and familiar interface.

  1. 1. Open WPS Writer: Launch WPS Office and open your letter template in WPS Writer.
  2. 2. Connect your Data Source: Go to the 'References' or 'Mailings' tab, click 'Open Data Source', and link your WPS Spreadsheet containing the enquiry dates.
  3. 3. Insert Merge Rules: Use the 'Insert Merge Field' and 'Rules' options to configure your custom text prompts and skip logic.
  4. 4. Complete the Merge: Click 'Merge to New Document' to automatically generate all the letters matching your selected criteria.
Free, lightweight, and fast office suiteFully compatible with Microsoft Word (.docx) and Excel (.xlsx) formatsIntuitive Mail Merge wizard with visual field mappingEasily apply sorting and filtering rules to recipient records
microsoft office alternative - wps office

Frequently Asked Questions

Why is the FILLIN prompt appearing for every single letter during the merge?

If you don't instruct Word to prompt only once, it will ask for input for every record in your database. Ensure you check the 'Ask once' box when creating the Fill-in rule, or manually add the '\o' switch to your field code (e.g., { FILLIN "Date of Enquiry" \o }).

How do I ensure the date format matches correctly between Word and Excel?

It is highly recommended to format the date column in your Excel document as 'Text' so that it doesn't dynamically change format. Alternatively, you can add a date formatting switch (like \@ "MM/dd/yyyy") to the MERGEFIELD code in Word to force a specific structure.

Can I use multiple prompts for different data fields in one document?

Yes, you can insert multiple FILLIN or ASK fields within the same document to prompt for different variables, such as enquiry date, user name, or specific regions, before completing the merge.