alert export csv cell formatting issue

[Lucente, Ransel Marie]

Hello,

In opsgenie when exporting csv for alerts, it generates columns "createdAt," CreatedAtDate," UpdatedAt," and "UpdatedAtDate" fields.  However it will not list the information properly.  I tried re-formatting the cells to date or time, but it still will not list the correct info.  How to change or re-format?  

Red outline is showing both date and time in formula bar, but reformatting does not reflect it in the actual cell.

Green outline is showing how it appears without re-formatting.

[John M]

Hi @Lucente, Ransel Marie ,

The CreatedAt and UpdatedAt fields in the Opsgenie export are formatted as unix time stamps. You can convert them with the following formula:

=DATE(1970, 1, 1) + (CELL_WITH_UNIX_DATE/ 86400000)

Be sure the cells with the unix dates are formatted as numbers, or the formula won't work. 

[Lucente, Ransel Marie]

I'm not following.  In my image, J5 says 21:40.0, but in the formula bar says 4/11/2024 09:21:40 AM.  How do I get the date and time into it's own cell?

[John M]

@Lucente, Ransel Marie the difference between the formula bar and the value shown in the cell would have to do with the formatting. We can't really advise on that since it's not related to the export, but rather the formatting of the spreadsheet editor that you're using.