Home / server / Questions / 1113763
Accepted
Dave Sopko
Asked: 2022-10-23 12:25:22 +0800 CST

Best way to convert azure logs date time into an excel date time

  • 772

Hello when I export Azure logs to csv I get this datetime format that excel doesn't recognize as a valid date time format:

2022-10-19T12:05:58Z

Is this the best way to convert this to an excel datetime or is there something simpler?

=DATE(MID(A107,1,4),MID(A107,6,2),MID(A107,9,2)) + 
    TIME(MID(A107,12,2),MID(A107,15,2),MID(A107,18,2)) 
    - TIME(5,0,0) 
    + TIME(1,0,0)

This minus five hours is to convert it into Easter Standard Time and the plus one hours is to account for daylight savings time.

azure azure-active-directory

1 Answers

  1. Best Answer

    All Azure log dates are in UTC. Take a look at:

    https://stackoverflow.com/questions/36456115/excel-how-to-convert-utc-date-time

    =--SUBSTITUTE(LEFT(A1,FIND("Z",A1)-1),"T"," ")

    • 1