Creating an end time based on an amount of time and a start time.
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.
Answers

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
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

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?
Help Article Resources
Categories
Check out the Formula Handbook template!