Formula to convert the UTC date/time column in the M365 CSV exports to Sydney time

[pedad]

I use the Microsoft 365 Admin Center exports and reports often, but am just exhausted by the mental process of converting a UTC time stamp in the CSV files to Sydney, AU time.
In most of the reports, there's an entire column for the zulu timestamp with upwards of 500 rows.

Are there any Excel whizzes who have a formula to convert 2020-08-12T05:24:05.9209092Z (for example) into something friendly? In Sydney, we're +10hrs on UTC.
Ideally I'd like hh:mm:ss dd-mm-yyyy formatted, but would even settle for the current format +10hrs.

 

[sandy666]

you can try with Power Query

Column1	Custom
2020-08-12T05:24:05.9209092Z	12/08/2020 17:24:06

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    TypeDT = Table.TransformColumnTypes(Source,{{"Column1", type datetime}}),
    DT_Au = Table.AddColumn(TypeDT, "Custom", each [Column1]+#duration(0,10,0,0)),
    TDT = Table.TransformColumnTypes(DT_Au,{{"Custom", type datetime}}),
    TSC = Table.SelectColumns(TDT,{"Custom"})
in
    TSC

 

[pedad]

I don't know how to use that?
Is it done in Excel?

Also, 5:24 AM UTC +10hrs is 3:24 PM AEST (or 15:24, not 17:24).

 

[FormR]

Hi, here is a formula you can try. Note, you may need to format the cell that contains the formula as date/time.

Book2
	A	B
2	2020-08-12T05:24:05.9209092Z	12/08/2020 15:24
Sheet1

Cell Formulas
Range	Formula
B2	B2	=SUBSTITUTE(LEFT(A2,19),"T"," ")+"10:00"

 

[sandy666]

my mistake, sorry

Column1	DTZAu
2020-08-12T05:24:05.9209092Z	12/08/2020 15:24:06

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    DTZAu = Table.AddColumn(Source, "DTZAu", each DateTimeZone.From([Column1])+#duration(0,10,0,0)),
    TDTZ = Table.TransformColumnTypes(DTZAu,{{"DTZAu", type datetimezone}})
in
    TDTZ

column DTZAu is formated: dd/mm/yyyy hh:mm:ss

this is M code for Power Query (aka Get&Transform) which is also built into Excel 365