ADDDAYS function doesn't mask dates with timestamps in Excel files
search cancel

ADDDAYS function doesn't mask dates with timestamps in Excel files

book

Article ID: 399927

calendar_today

Updated On:

Products

CA Test Data Manager (Data Finder / Grid Tools)

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