ADDDAYS function doesn't mask dates with timestamps in Excel files
Article ID: 399927
Updated On:
Products
Issue/Introduction
We are using FDM 4.10.1014.0 and the ADDDAYS function for adding 10 days to a date only (MM/DD/YYYY) format, and it worked as expected. However, we now have a use case where we need to do the same in an Excel file, but the date field also contains a timestamp (MM/DD/YYYY hh:mm:ss). When running the masking job to add 10 days to the date and timestamp, the masking completes, but the dates in the scramble file are not masked.
For example:
- DATE.xls (original file):
DATE 01/31/1932 12:00:00 02/09/1940 12:00:00 08/02/2029 12:00:00 06/28/2026 12:00:00 01/05/1944 12:00:00 08/07/1938 12:00:00 07/29/1934 12:00:00 01/23/1944 12:00:00 - DATE.DM.TXT (Definition File):
HEADER=,TRAILER=,DELIM=EXCEL,COLUMNROW=Y
DATE,"MM/DD/YYYY HH:MM:SS" - Masking Config:
Table,Column,Function,Parm1,Parm2,Parm3,Parm4,Keep Nulls,Date Format,Cross Reference,Override SQL,Unique Columns,XPath Element,Substr start,Substr length,Notes,Preformat,Update,Use Masked Values,Restart Column,From Occurrence,To Occurrence,Parm5,Parm6,Parm7,Parm8,Parm9,Parm10
DATE.DM.TXT,DATE,ADDDAYS,10,,,,Y,MM/DD/YYYY HH:MM:SS,,,,,,,,,,,,,,,,,,, - AUDIT Log from FDM:
MAPPING FILE ARCHIVED AS: C:/Users/<user>/AppData/Roaming/Grid-Tools/FastDataMasker/backups/Date_Time_masking.20250603115619.csv
OPTIONS:
MASKING STARTED AT: 2025.06.03 11:56:19.231 CDT
LINE,MASK COLUMN,FUNCTION,OLDVALUE,NEWVALUE
2,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","01/31/1932 12:00:00","02/10/1932 12:00:00"
3,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","02/09/1940 12:00:00","02/19/1940 12:00:00"
4,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","08/02/2029 12:00:00","08/12/2029 12:00:00"
5,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","06/28/2026 12:00:00","07/08/2026 12:00:00"
6,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","01/05/1944 12:00:00","01/15/1944 12:00:00"
7,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","08/07/1938 12:00:00","08/17/1938 12:00:00"
8,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","07/29/1934 12:00:00","08/08/1934 12:00:00"
9,"DATE,"MM/DD/YYYY HH:MM:SS"","ADDDAYS","01/23/1944 12:00:00","02/02/1944 12:00:00" - DATE(scramble).xls:
The created scramble file still contains the original dates. We expected to see the new values shown in the audit log.
DATE 01/31/1932 12:00:00 02/09/1940 12:00:00 08/02/2029 12:00:00 06/28/2026 12:00:00 01/05/1944 12:00:00 08/07/1938 12:00:00 07/29/1934 12:00:00 01/23/1944 12:00:00
Environment
FDM 4.10.x through FDM 4.11.176.0
Resolution
This is resolved in FastDataMasker-4.11.178.0 or greater
TDM and FDM patches are available for download from the Test Data Manager (TDM) Support Patches page.
Additional Information
Feedback
