# 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

• ✭✭✭✭✭✭

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.

#### 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

=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.

Now we have this

#### To add the days and hours to the original start date

This formula will add the days

#### 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)

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:

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

• ✭✭✭✭✭✭
edited 03/15/24

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