Help with time calculation (total hours)

Xochitl C.

I'm desperate for calculating hours help 😩

I've created a report sheet for incidents in our department. These are submitted using a form and the incidents are managed by several people. The idea is that depending on the severity of the incident, we should alert the incident owner so they can update the status and leave notes after 3 hours and after 24 hours of the incident being created. (the workflow will trigger the alert when flag is checked)

I have a system column that indicates the submission date/time and I have another column to calculate the total of hours elapsed from the submission date to the present date using TODAY formula. The problem is that after doing a test this morning, the formula is not counting correctly the hours elapsed. As you can see in the screenshot below the submission was at (8:55 AM and it now 12:16 PM, so its more that 3 hour and I get zero.

Am I doing something wrong?

More context of how the flags are configured:

NOTIF FLAG (+3 hours):

NOTIF FLAG (+24 hours):

Any insight is much appreciated!!! 😊

jmyzk_cloudsmart_jp

Hi @Xochitl C.

You're very close! Here's a simple explanation of what's happening and how to fix it:

Issue 1: Smartsheet uses UTC, not your local time

• Smartsheet calculates time formulas like TODAY() based on UTC or GMT.
• The Created column shows your local time.
• Because of this, if your time zone is different from UTC, the hour difference will look wrong.
• Solution: Extract the year, month, day, and hour separately from the Created field using TEXT functions, and use TIME to correctly handle AM/PM.

Please look at the Test1 and Test 2 rows in the image below. The Created column's values both show April 9th, but the DATEONLY values are April 8th before 9:00 AM and April 9th for the Text 2 row after 9 AM. The time zone caused the difference. Tokyo, JST, which is GMT +9, so anytime before 9 AM in Greenwich is yesterday.

Issue 2: Same-day submissions show "0 hours"

• If someone submits today, TODAY() - Created = 0 days, and multiplying by 24 gives 0 hours even though several hours have passed.
• Solution: Use the Modified column to get the latest timestamp, then calculate the difference using the latest hour instead of only the date.

For example, the 16th row vs the April 8th column shows 0, even though time zone issues are adjusted.

https://app.smartsheet.com/b/publish?EQBCT=5114fd958688478a8e10b81b6e2db3d9

( The [Incident Start (Created Date)] column's values are text values OCRed from your sheet image. )

Here's how you can set up the formulas:

Helper columns:

[Y] = VALUE(20 + MID([Incident Start (Created Date)]@row, 7, 2))

[M] = VALUE(MID([Incident Start (Created Date)]@row, 1, 2))

[D] = VALUE(MID([Incident Start (Created Date)]@row, 4, 2))

[H] = VALUE(MID(TIME(MID([Incident Start (Created Date)]@row, 10, LEN([Incident Start (Created Date)]@row) - 9), 1), 1, 2))

To calculate TOTAL HOURS versus a fixed date like April 8th:

=(TODAY(Diff#) - DATE(Y@row, M@row, D@row)) * 24

Diff# is a Sheet Summary Field that calculates the difference between today and April 8th.

To calculate TOTAL HOURS since the submission, fixing the same-day issue:

=(YEAR(TODAY()) * 365 + MONTH(TODAY()) * 30 + DAY(TODAY()) - Y@row * 365 - M@row * 30 - D@row) * 24 + [H (Latest)]# - H@row

Sheet Summary field formula for [H (Latest)]#:

=VALUE(MID(TIME(MID(Modified1, 10, LEN(Modified1) - 9), 1), 1, 2))

Explanation:

• [Y], [M], [D], [H] extract year, month, day, and hour from the Created field.
• [H (Latest)]# extracts the latest Modified hour across the sheet.
• You calculate the total hour difference by combining the date and hour differences separately.

Summary:

• Smartsheet uses UTC for TODAY() — so you need to handle time manually.
• Extract time parts carefully.
• Use Modified to get the latest hour so same-day incidents do not show 0 hours.
• This way, your +3 hour and +24 hour flags will work properly.

Here's Smartsheet's official article explaining this behavior:
https://help.smartsheet.com/articles/2482412-how-smartsheet-handles-time-zones