Link to home
Start Free TrialLog in
Avatar of Troy Graham
Troy GrahamFlag 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.
Avatar of Troy Graham
Troy Graham
Flag of Australia image


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"

Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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