Creating an end time based on an amount of time and a start time.

Keenan Kibrick

I am trying to create a formula so that if I start something at 7:00 AM and then say it will be 30 minutes long, an end time column created at the would display 7:30 AM.

I looked through all the time formulas and could not get any to work properly. I admit that might be my lack of formula knowledge. I know already that Smartsheet has problems with time, but if anyone can help me make a formula for this I would greatly appreciate it.

Bassam Khalil

Hi @Keenan Kibrick

Hope you are fine, i created a sample sheet for your question, and the following is the formula to calculate end time using the created time ( System column created ) and duration.

1- Hours Column =VALUE(MID(Created@row, 12, 2)) convert to column formula

2- Minutes Column =VALUE((MID(Created@row, 15, 2))) convert to column formula

3- Total Minute =IF((Duration@row + Minutes@row) > 60, (Duration@row + Minutes@row) - 60, (Duration@row + Minutes@row)) convert to column formula

4- Total Hours =IF((Duration@row + Minutes@row) > 60, ROUND((Duration@row + Minutes@row) / 60)) + VALUE(Hours@row) convert to column formula

5- Period =MID(Created@row, 18, 5) convert to column formula

6- End Time =(IF([Total Hours]@row < 10, "0" + [Total Hours]@row, [Total Hours]@row) + ":" + IF([Total Minute]@row < 10, ("0" + [Total Minute]@row), [Total Minute]@row + " " + Period@row)) convert to column formula

the following screenshot show the sample sheet

Alisia Anderson

I am doing something similar but far less complex b/c the time is a manual entry in Column A (let's say 7pm). I would like to use a formula to show 3 hours prior to the time in Column A (show 4pm in Column B). How would I do that? Would I use the same logic as above and create helper columns to extract hour, subtract by 3, and then show the final?