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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!