We help IT Professionals succeed at work.

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.
Comment
Watch Question

Troy GrahamIT Manager

Author

Commented:

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"

Finance Analyst
Commented:
Try this:
=VALUE(SUBSTITUTE(LEFT(A2,FIND(".",A2,1)-1),"T"," "))

Use custom format "dd/mm/yyyy hh:mm:ss"
Rob HensonFinance Analyst

Commented:
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