Excel - Convert UTC time in cell to AEST and place output in another cell.
I am exporting some logs from Microsoft Azure to an Excel CSV file and I would like to convert UTC time that is placed in cell A2 to AEST and place in B2.
I would prefer not to have to use a third party tool, so if there is a formula I can use would be great.
The Date Value in cell A2 look like this "2020-02-11T22:49:56.16995
Furthermore, if this cant be done is there a way that I can change the timezone in Azure so when I export the logs the dates are shown in EST ?
Any help would be appreciated.
I would prefer not to have to use a third party tool, so if there is a formula I can use would be great.
The Date Value in cell A2 look like this "2020-02-11T22:49:56.16995
Furthermore, if this cant be done is there a way that I can change the timezone in Azure so when I export the logs the dates are shown in EST ?
Any help would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Your formula was working "correctly" but didn't need all of it.
The SUBSTITUTE part was creating the correct date and time and then the MID function was creating the date again but dividing it by 24 and adding it onto the date and time, in this case increasing the date by 1905 days/5 years
The SUBSTITUTE part was creating the correct date and time and then the MID function was creating the date again but dividing it by 24 and adding it onto the date and time, in this case increasing the date by 1905 days/5 years
ASKER
I am close as I have come up with this formula
=(SUBSTITUTE(LEFT(A2,27),"T"," "))+(MID(A2,1,10)/24)
but it doesnt convert date or time properly as it is showing "12/02/2025 22:49" for a value that has this "2020-02-11T22:49:31.1967548Z"