Avatar of Troy Graham
Troy Graham
Flag for Australia asked on

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.1699545Z" and I would like it to look like this in Cell B2 "11/02/2020 22:49:56"

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.
AzureMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Rob Henson

8/22/2022 - Mon
Troy Graham


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"

Rob Henson

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Rob Henson

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck