How to convert Date and time to Pacific Standard or Pacific Daylight Time
Answers
-
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
-
If you already have the hour in your data set, then try this:
- Setup a column in your sheet called Offset with the following formula:
=INDEX({Offset}, MATCH([Time Zone]@row, {Time Zone}, 0))
- 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
- 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.
- Right click that formula and choose Convert to Column Formula.
- 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)))
- 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.
- Setup a column in your sheet called Offset with the following formula:
-
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,
-
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.
-
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.
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives