CODEWITHSUNDEEP
excel
user145416
user145416

Reputation: 173

How to convert UTC to PST in Excel for day and time formats?

In excel, I have a cell which is

13 05:58:57

which represents day 13, 5 am, 58 minutes, 57 seconds in UTC time.

I would like to convert to PST, which should be

12 10:58:57

which is day 12, 10 pm, 58 minutes, 57 seconds.

The day is just a day of the month. Is there a way to do this quickly in excel?

Upvotes: 1

Views: 1729

Answers (1)

zsalya
zsalya

Reputation: 454

Excel stores date_times as numbers, where each day is 1, and the decimal part of the number is the time (so 0.25 is 6 hours, and 0.7 is 16 hours + 48 minutes)
Time 0 is the beginning of 1899/12/31, so =today() formatted as a decimal currently shows me as 44730.4556, being 44730 days since 1899 and almost eleven a.m.

Once you have your data in that format it is trivial to E.g.:

  • subtract 7 hours = A2 - 7/24
  • add 2 days 6 hours and 15 minutes = A2 + 2 + 6/24 + 15/24/60
  • find the difference between two date_times

So you want to get the input data into that form, from which point you just use formatting. To see where you are going, enter in A1 = now(), in B1 =A1, in C1 = B1.
Change the format on B1 - Right Click | Format Cells | Custom | yyyy-mm-dd hh:mm:ss.
Then in C1 try changing to a format you want: dd hh:mm:ss AM/PM.
For a full list of options see https://support.microsoft.com/en-au/office/format-numbers-as-dates-or-times-418bd3fe-0577-47c8-8caa-b4d30c528309

I will assume that your data starts at A4, but I can't tell what is actually stored in the cell, as opposed to what it displays.
If your input data is actually already a date_time (what does it look like if you format is as a decimal?) all you need to do is =A4-7/24.
If your input data is actually a string, then separate it into day, hour, minute, second in C, D, E, F.
If single-digit days have leading zeros then just =left(A4,2) then =mid(A4,4,2) etc.
If there are no leading zeros put in column B =find(" ",A4) and point the lefts and mids to that intermediate result.
Then in G put = C4 + D4/24 + E4/24/60 + F4/24/60/60 so you have the input data as an Excel-formatted time.
And subtract the 7 hours difference with =G4-7/24

P.S. If you need to cope with month-ends and DST then you need to add year and month to the data in G - currently it has date_times in January 1899.

Upvotes: 2

Related Questions