How to convert Date and time to Pacific Standard or Pacific Daylight Time

System

This discussion was created from comments split from: Help to set a time column to CST time zone.

Omar de Armas

Hi @Bassam Khalil

I have a similar situation that your solution may apply to, but I have reports that come in from all over the world and need to have the Date and time converted to Pacific Standard or Pacific Daylight Time. I already included separate fields in the intake form for Date, Hour, and minutes (since the time a report is entered is not the date and time the incident occured, I can not use the timestamp) and have a separate table with the Time Zone and offset to use for each one. Any help you can provide would be greatly appreciated.

Thank you,

Omar

Brian_Richardson

If you already have the hour in your data set, then try this:

1. Setup a column in your sheet called Offset with the following formula: =INDEX({Offset}, MATCH([Time Zone]@row, {Time Zone}, 0))
   1. This assumes you are also having people capture the Time Zone as part of your data, in a dropdown column where the values match your lookup sheet
   2. The {} items are cross-sheet references, inserted into your formula by clicking Reference Another Sheet in the formula popup box when you're typing the formula out.
2. Right click that formula and choose Convert to Column Formula.
3. Setup a column called CST Time with the following formula: =IF(Hour@row + Offset@row < 0, Date@row - 1 + " " + TIME(24 - Hour@row + Offset@row, Minutes@row, 0, 0, 2), IF(Hour@row + Offset@row > 24, Date@row + 1 + " " + TIME(Hour@row + Offset@row - 24, Minutes@row, 0, 0, 2), Date@row + " " + TIME(Hour@row + Offset@row, Minutes@row, 0, 0, 2)))
4. Right click that formula and choose Convert to Column Formula

The formula basically checks to see if applying the offset will push the time <0 or >24 and if so it adjusts both the date and the time offset. Otherwise, it's a simple offset application to the hours and minutes that you're already capturing.