Date Conversion to UTC in Excel-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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")