Excel formula to convert special date/time format to UTC

Question

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!

Answer 1

Not sure how to account for the day dropping below 0 based on your question, but the below will account for change in the day if the value is in A11:

=IF(TIMEVALUE(RIGHT(A11,8))-(7/24)<0,CONCAT(LEFT(A11,LEN(A11)-9)-1," ", TEXT(1+TIMEVALUE(RIGHT(A11,8))-(7/24),"hh:mm:ss")),CONCAT(LEFT(A11,LEN(A11)-9)," ", TEXT(TIMEVALUE(RIGHT(A11,8))-(7/24),"hh:mm:ss")))

Answer 2

I'd add the current month and year to the day and time to get a real date/time and then subtract 7 hours. This will give different numbers if you use day 1 - it will return the last day of the previous month.

With your original value in A1:

=SUM(DATE(YEAR(TODAY()),MONTH(TODAY()),LEFT($A$1,FIND(" ",$A$1)-1)),TIMEVALUE(MID($A$1,FIND(" ",$A$1)+1,LEN($A$1))))  

You could replace TODAY() in the formula with any valid date value.