Excel formula to convert special date/time format to UTC

%3CLINGO-SUB%20id%3D%22lingo-sub-2334652%22%20slang%3D%22en-US%22%3EExcel%20formula%20to%20convert%20special%20date%2Ftime%20format%20to%20UTC%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2334652%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20dataset%20with%20the%20date%2Ftime%20represented%20in%20UTC%20as%20%22d%20hh%3Amm%3Ass%22.%20For%20example%20%2212%2004%3A14%3A15%22.%20How%20do%20I%20convert%20this%20to%20a%20PDT%20format%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20I%20am%20having%20is%20because%20the%20data%20does%20not%20have%20month%20or%20year%2C%20if%20I%20split%20the%20date%20and%20time%20into%20separate%20columns%20and%20subtract%207%20hours%20from%20the%20time%20column%2C%20this%20does%20not%20capture%20the%20potential%20change%20in%20date.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAny%20suggestions%20greatly%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2334652%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2335181%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20formula%20to%20convert%20special%20date%2Ftime%20format%20to%20UTC%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2335181%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047723%22%20target%3D%22_blank%22%3E%40rev000%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DLET(PDT%2C%20-7%2C%0A%20%20%20%20%20date%2C%20DATE(YEAR(TODAY())%2C%20MONTH(TODAY())%2C%20LEFT(A1%2C2))%2C%0A%20%20%20%20%20time%2C%20TIMEVALUE(RIGHT(A1%2C8))%2C%0A%20%20%20%20%20timePDT%2C%20time%2BPDT%2F24%2C%0A%20%20%20%20%20newDay%2C%20DAY(date)%20-%20(timePDT%26lt%3B0)%2C%0A%20%20%20%20%20newTime%2C%20timePDT%2B(timePDT%26lt%3B0)*24%2C%0A%20%20%20%20%20newDay%20%26amp%3B%20%22%20%22%20%26amp%3B%20TEXT(newTime%2C%22hh%3Amm%3Ass%22))%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Ebut%20that's%20for%20current%20year%20and%20month%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

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!

1 Reply

@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