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.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.
AzureMicrosoft ExcelMicrosoft Office
Last Comment
Rob Henson
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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
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"