Date Conversion to UTC in Excel

Question

I have the below dates received in an Excel cell. I want them to be subtracted from 8 hours from the given date

2022-03-16T23:02:14+08:00
2022-03-17T07:59:46+08:00
2022-03-17T08:00:34+08:00

Here, I need output as for Eg:

2022-03-16
2022-03-16
2022-03-17

Where 8 hours is 1/3 of a day, so use a formula that has -1/3 — James McLeod, Mar 23 '22 at 08:36

score 1 · Accepted Answer · answered Mar 23 '22 at 08:32

1

Using helper columns to explain what happens:

Column B returns the plain date
Column C returns the time part
Column D adds both values and subtracts the 8 hours (calculated as a decimal of 24 hours). INT is used to only return the date part - without time part.

answered Mar 23 '22 at 08:32

Ike

It worked using formula =INT(DATEVALUE(MID(L2,1,10))+TIMEVALUE(MID(L2,12,8))-8/24) – Chetan Gadodia Mar 23 '22 at 08:54
Perfect - I used the helper columns to visualize the steps - but obviously you can merge all into one formula – Ike Mar 23 '22 at 09:14

score 0 · Answer 2 · answered Mar 23 '22 at 08:35

0

If your dates are stored as string in excel cells then you may use below formula-

=TEXT(SUM(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A1,"+","</s><s>"),"T","</s><s>")&"</s></t>","//s[position()<=2]"))-TIME(8,0,0),"yyyy-mm-dd")

answered Mar 23 '22 at 08:35

Harun24hr

2 Answers2