Forum Discussion

rev000's avatar
rev000
Copper Contributor
May 07, 2021

Excel formula to convert special date/time format to UTC

I have a dataset with the date/time represented in UTC as "d hh:mm:ss". For example "12 04:14:15". How do I convert this to a PDT format?   The problem I am having is because the data does not have...
excel
Formulas and Functions
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
May 07, 2021

rev000 

As variant

=LET(PDT, -7,
     date, DATE(YEAR(TODAY()), MONTH(TODAY()), LEFT(A1,2)),
     time, TIMEVALUE(RIGHT(A1,8)),
     timePDT, time+PDT/24,
     newDay, DAY(date) - (timePDT<0),
     newTime, timePDT+(timePDT<0)*24,
     newDay & " " & TEXT(newTime,"hh:mm:ss"))

but that's for current year and month

Resources