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 month or year, if I split the date and time into separate columns and subtract 7 hours from the time column, this does not capture the potential change in date.

 

Any suggestions greatly appreciated!

excel
Formulas and Functions

1 Reply

  • 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