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.

R Lewis

Hi @Bassam Khalil

Hi @Brian_Richardson

I need to convert time zones from UTC, CET to PST in Smartsheet.

I am trying the formulas you suggested above but keep getting #invalid operation errors.

For the PST Time column, I am trying this formula:

=IF([Start Hour]@row + [Time Zone Offset]@row < 0, [Start Date]@row - 1, +" " + TIME(24 - [Start Hour]@row + [Time Zone Offset]@row, [Start Minutes]@row, 0, 0, 2))

For the CST Time column, I am trying this formula:

=IF([Start Time]@row + ([Time Zone Offset]@row) < 0, [Start Date]@row - 1 + "" + TIME([Start Time]@row + ([Time Zone Offset]@row) - 24, [Start Minutes]@row, 0, 0, 2), IF([Start Time]@row + ([Time Zone Offset]@row) > 24, [Start Date]@row + 1 + "" + TIME([Start Time]@row + ([Time Zone Offset]@row) - 24, [Start Minutes]@row, 0, 0, 2), [Start Date]@row + "" + TIME([Start Time]@row + ([Time Zone Offset]@row), [Start Minutes]@row, 0, 0, 2)))

Can you help and show me what I am doing wrong?

Thank you,

R Lewis

More information:

I am also getting an #invalid column value error message when referencing the Start Date column as

[Start Date]@row. I have tried setting the column as text/number and as Date, but get the same message for both.

Brian_Richardson

Incorrect Argument usually means a mistype in the formula.

You cannot take a time like “7:00AM” and attempt to add or subtract from it, you’re trying to do math on text and therefore is probably why you’re getting invalid operation/value errors. Notice in my example that the hours and minutes were separated out into columns so they could be numbers.