Date Time Excel Filter Help

Options
Nasir_168130
Nasir_168130 Posts: 17
in Questions

I have an excel file I would like to filter for a specific date time of 1/16/2020 12:00:00 AM. When I enter that as my filter criteria into the action, and then run it, I do not get the filtered data set I am expecting, even though the data is displayed in that exact way. I tried converting the file to a table to see if there is a formatting issue, and it looks like it is formatting the date to be displayed as 2020-01-16T00:00:00Z when the filter excel file step is running causing the filtered data set to be empty. How can I accommodate the current way the data is displayed for the filter to work and not have to have a workaround?

Tagged:

Best Answer

  • Brandon_189578
    Brandon_189578 Posts: 36
    Answer ✓
    Options

    Nasir,

    If the excel file is not a .csv then what you are seeing in Excel is the representation of the date time according to the cell's formatting properties. You can work around this in two ways:

    • use the 2020-01-16T00:00:00Z format (which is how the underlying date is stored, regardless of how Excel renders it to you).
    • export the xlsx to a csv using "Excel: Convert to CSV", making sure to set Retain formatting to true. This will cause the Excel rendered version to be what is exported, instead of the date as it is stored in the sheet.

    Regards,
    Brandon

Categories