How to calculate X hours from the Created Date time stamp
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
Answers

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.

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.

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"))
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.7K Get Help
 63 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 55 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!