How to calculate X hours from the Created Date time stamp

MelissaSan

Hi,

I am trying to create a formula that will add 5 hours to the Request Start Date (which I am using the created date for) if Priority = critical.

=IF([Priority]@row = "Critical", [Request Start Date]@row + (5 / 24), IF([Priority]@row = "High", [Request Start Date]@row + 1, IF([Priority]@row = "Low", [Request Start Date]@row + 2, "")))

So in the example:

Request Start Date = 3/14/24 3:15 pm

Priority = Critical

This formula is returning 3/15/24 rather than 3/14/24 8:15pm

I need the time stamp to be returned

Thanks,

Melissa

KPH

Unfortunately, there is not a timestamp format in smartsheet. The Created Date and Modified Date columns are the only ones that include times. You can't easily add 5 hours to either.

The workaround is to split the date and time from the Created Date column into two separate columns and then do the math on those, then join them back into a text column. You are going to need to combine a few formula so I will explain each one in turn.

TLDR: Skip to the end for the two formula.

To extract the time from Request Start Date

Use this formula:

=RIGHT([Request Start Date]@row, 8)

To extract the date from Request Start Date

You could use this formula:

=DATEONLY([Request Start Date]@row)

HOWEVER, this only works if you are in UTC timezone. For anywhere else, it is safer to make your own date from the text in the Request Start Date. This formula will do that:

=DATE(VALUE(20 + MID([Request Start Date]@row, 7, 2)), VALUE(LEFT([Request Start Date]@row, 2)), VALUE(MID([Request Start Date]@row, 4, 2)))

So now you have formula to do this:

Convert the time into a number

As you can see, the time is a text string, so we still cannot add 5 hours. So we need to convert the time we extracted into a number. I can see from your example that you are using the 12 hour clock. This formula will give a numeric value for how many hours the timestamp is past midnight.

=IF(VALUE(LEFT(CreatedTime@row, FIND(":", CreatedTime@row) - 1)) <> 12, IF(CONTAINS("p", CreatedTime@row), 12), IF(CONTAINS("a", CreatedTime@row), -12)) + VALUE(LEFT(CreatedTime@row, FIND(":", CreatedTime@row) - 1)) + (VALUE(MID(CreatedTime@row, FIND(":", CreatedTime@row) + 1, 2)) / 60)

Add 5 hours to the time

Add +5 to add the 5 hours for your Critical status.

=IF(VALUE(LEFT(CreatedTime@row, FIND(":", CreatedTime@row) - 1)) <> 12, IF(CONTAINS("p", CreatedTime@row), 12), IF(CONTAINS("a", CreatedTime@row), -12)) + VALUE(LEFT(CreatedTime@row, FIND(":", CreatedTime@row) - 1)) + (VALUE(MID(CreatedTime@row, FIND(":", CreatedTime@row) + 1, 2)) / 60) + 5

Now we need to convert this back into a date and time

To find the number of days in the hours

In the example above, the total hours to add are 26.15 and 27.

This formula will evaluate the total hours to add and give you the number of whole days

=INT([total hours to add]@row / 24)

To find the number of hours in the hours excluding full days

We can use this formula.

=[total hours to add]@row - ([days to add]@row * 24)

Now we have this

To add the days and hours to the original start date

This formula will add the days

=CreatedDate@row + [days to add]@row

To find the time from the hours

This formula will convert the hours into a timestamp

=MOD(INT([hours to add]@row), 12) + ":" + IF(([hours to add]@row - INT([hours to add]@row)) * 60 < 10, "0") + ([hours to add]@row - INT([hours to add]@row)) * 60 + IF([hours to add]@row >= 12, " PM", " AM")

To build the timestamp

Combine these formula

=[date ]@row + " " + time@row

Combine all the formula

I suggest keeping the total hours to add as a separate column (to keep the formula manageable and enable you to adjust the +5 easily if needed), and using that and the Request Start Date like this to calculate the Critical Date/Time.

Critical hours (helper)

=IF(VALUE(LEFT(RIGHT([Request Start Date]@row, 8), FIND(":", RIGHT([Request Start Date]@row, 8)) - 1)) <> 12, IF(CONTAINS("p", RIGHT([Request Start Date]@row, 8)), 12), IF(CONTAINS("a", RIGHT([Request Start Date]@row, 8)), -12)) + VALUE(LEFT(RIGHT([Request Start Date]@row, 8), FIND(":", RIGHT([Request Start Date]@row, 8)) - 1)) + (VALUE(MID(RIGHT([Request Start Date]@row, 8), FIND(":", RIGHT([Request Start Date]@row, 8)) + 1, 2)) / 60) + 5

Critical Date/Time (result)

=(DATE(VALUE(20 + MID([Request Start Date]@row, 7, 2)), VALUE(LEFT([Request Start Date]@row, 2)), VALUE(MID([Request Start Date]@row, 4, 2))) + INT([total hours to add]@row / 24)) + " " + (MOD(INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24)), 12) + ":" + IF(([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) - INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24))) * 60 < 10, "0") + ([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) - INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24))) * 60 + IF([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) >= 12, " PM", " AM"))

You can then put this into your IF formula.

You can then duplicate the Critical hours column and adjust the number of hours to add, for other priorities.

MelissaSan

Thank you so much for this. I'm still having a hard time replicating it and getting #unparsable when I use the last formula.

I know you broke it down, however, I am now confused on how many helper columns I need. Do i need "total hours to add", "days to add" etc?

Then when I put it in my IF statement, nothing works:

=IF([Priority]@row = "Critical", =(DATE(VALUE(20 + MID([Request Start Date]@row, 7, 2)), VALUE(LEFT([Request Start Date]@row, 2)), VALUE(MID([Request Start Date]@row, 4, 2))) + INT([total hours to add]@row / 24)) + " " + (MOD(INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24)), 12) + ":" + IF(([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) - INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24))) * 60 < 10, "0") + ([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) - INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24))) * 60 + IF([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) >= 12, " PM", " AM")), IF([Priority]@row = "High", [Request Start Date]@row + 1, IF([Priority]@row = "Low", [Request Start Date]@row + 2, "")))

I can't figure out what i'm doing wrong.

KPH

You could do it all in one column but I suggest using two to make it more manageable. I would get these two working first. Then put your IFs around the result column.

Critical hours (helper) - I call this "total hours to add" and it is used in the next formula

=IF(VALUE(LEFT(RIGHT([Request Start Date]@row, 8), FIND(":", RIGHT([Request Start Date]@row, 8)) - 1)) <> 12, IF(CONTAINS("p", RIGHT([Request Start Date]@row, 8)), 12), IF(CONTAINS("a", RIGHT([Request Start Date]@row, 8)), -12)) + VALUE(LEFT(RIGHT([Request Start Date]@row, 8), FIND(":", RIGHT([Request Start Date]@row, 8)) - 1)) + (VALUE(MID(RIGHT([Request Start Date]@row, 8), FIND(":", RIGHT([Request Start Date]@row, 8)) + 1, 2)) / 60) + 5

Critical Date/Time (result)

=(DATE(VALUE(20 + MID([Request Start Date]@row, 7, 2)), VALUE(LEFT([Request Start Date]@row, 2)), VALUE(MID([Request Start Date]@row, 4, 2))) + INT([total hours to add]@row / 24)) + " " + (MOD(INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24)), 12) + ":" + IF(([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) - INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24))) * 60 < 10, "0") + ([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) - INT([total hours to add]@row - (INT([total hours to add]@row / 24) * 24))) * 60 + IF([total hours to add]@row - (INT([total hours to add]@row / 24) * 24) >= 12, " PM", " AM"))