This article explains how to troubleshoot and resolve an exported report in CSV format when every row for a date metadata field contains the same incorrect value (for example, 0001-01-01 16:10:58), even though the Pendo UI shows the correct value.
Product area: Any report that you can export as a CSV file, including exports requested through the Pendo UI and API (web, mobile)
Issue
When sending metadata to Pendo, it must match one of the supported date formats, as described in Configure visitor and account metadata. If metadata is sent in the date format MM/DD/YYYY or any other unsupported date formats, Pendo can’t process the date value into milliseconds, which is required for Pendo to have a standardized time format in the backend. It also causes two major issues:
- The metadata field can't use any of the segmentation rules for dates.
- The metadata field can’t be converted correctly for report exports in CSV format. This means that all rows in the report will have the same incorrect value in the format YYYY-MM-DD HH:MM:SS (for example, 0001-01-01 16:10:58), even if the values in the Pendo UI are unique and correct.
Solution
There are three options for resolving incorrect date values in this scenario.
Option 1. Change date format in install script (recommended)
The first option resolves both the report export issue and not being able to use segmentation rules for dates.
- Ask your developers to make changes as needed to send date values to Pendo in a supported date format, as described in Configure visitor and account metadata. We recommend the ISO8601 W3C format (for example, 2006-01-02T15:04:05.999-05:00) for dates, which is the default in Settings > Data Mappings. Contact Pendo Support if you need to choose a different supported date format.
- We recommend sending the corrected date values as a new metadata field instead of reusing the existing metadata field. This ensures that all values in the new metadata field are using the supported date format and processing correctly.
Option 2. Change metadata type in Data Mapping settings
The second option fixes the issue with report exports, but it still doesn't allow you to use segmentation rules for dates. You must be a Pendo admin to follow these steps:
- In Settings > Data Mappings, change the Type for the metadata field from Date to Text.
- Wait at least 6 hours for caching to expire, then try exporting the report as a CSV file again.
Option 3. Change date format in Google Sheets or Excel
The third option fixes the issue with report exports, but it requires you to do this upon each export and still doesn't allow you to use segmentation rules for dates.
To convert the format from ISO8601 W3C to date format in Google Sheets, select the cells with the incorrect date formats, then select Format > Number > Date time.
To convert the ISO8601 W3C format in Excel:
- Enter the following formula into the first cell of an empty column: =DATEVALUE(MID(A1,1,10))+TIMEVALUE(MID(A1,12,8)).
- Select the cell, then drag the fill handle to the end of the column.
- Right-click on the column header, then select Format Cells...
- Select the correct date format.