Calculate time by adding hours and minutes
I want to create an agenda with start times. I want to specify the start time for the fist item and then have Smartsheet calculate the start times of the remaining items based on how many minutes are assigned to the previous item. Any ideas how to do this?
In excel, I’m able to use 0:00:00 AM/PM format and SUM them.
I’m struggling with this in smartsheet.
Thanks.
Best Answers

Hi Pam,
I would suggest creating a helper column called Minutes, along with Start Time and duration columns.
Set the formula in the Minutes column to be the sum of the Minutes and Duration from the row above (except for the first row, which you could set to a reference to a cell somewhere else on the sheet, where you can enter the start time in minutes (eg 540 for 9 am).
The Minutes column is now the start time you need in minutes, but not user friendly. Put the follwoing formula into the Start Time column,
=ROUND(Minutes@row / 60) + ":" + RIGHT("0" + (Minutes@row  60 * ROUND(Minutes@row / 60)), 2)
and this will give you something more understandable.
Now hide the Minutes column....
Unfortunately Smartsheet does not seem to have any time manipulation functions....
Regards,
Andrew

Not sure if you got this sorted out or not... but I had the same need so I tried the answer here with the same result... so close to working, but in some scenarios it didn't. I realized the problem was when the ROUND function led to the next hour because the result was more than x.5... so, I changed the function to use ROUNDDOWN in place of ROUND... now it works perfectly.
Answers

Hi Pam,
I would suggest creating a helper column called Minutes, along with Start Time and duration columns.
Set the formula in the Minutes column to be the sum of the Minutes and Duration from the row above (except for the first row, which you could set to a reference to a cell somewhere else on the sheet, where you can enter the start time in minutes (eg 540 for 9 am).
The Minutes column is now the start time you need in minutes, but not user friendly. Put the follwoing formula into the Start Time column,
=ROUND(Minutes@row / 60) + ":" + RIGHT("0" + (Minutes@row  60 * ROUND(Minutes@row / 60)), 2)
and this will give you something more understandable.
Now hide the Minutes column....
Unfortunately Smartsheet does not seem to have any time manipulation functions....
Regards,
Andrew

Hi Andrew
Thank you for taking the time to answer this question! The formula worked for the first 2 cells and then it broke because I need to figure out how to add a "duration column" to my smartsheet. I realize if I had started it as a "project", that column would already be there but I did not. :(

Thank you @Andrew Stewart !
Your solution worked perfectly.

The formula stops working correctly between 10:25 and 10:35. See my example. The bottom start time should be 10:35. Here is my formula on the "11:25" result:
=ROUND(Minutes@row / 60) + ":" + RIGHT("0" + (Minutes@row  60 * ROUND(Minutes@row / 60)), 2)
Or, time goes backwards. Here are rows 10 and 11. My minutes formula in row 11 is =Minutes10 + [Minutes in Current Agenda]10 but I get a start time of 13:15 instead of 13:35.
And just one last anomaly... some of the times have a "" in the minutes. This is the formula:
=ROUND(Minutes@row / 60) + ":" + RIGHT("0" + (Minutes@row  60 * ROUND(Minutes@row / 60)), 2)
Any thoughts?

Not sure if you got this sorted out or not... but I had the same need so I tried the answer here with the same result... so close to working, but in some scenarios it didn't. I realized the problem was when the ROUND function led to the next hour because the result was more than x.5... so, I changed the function to use ROUNDDOWN in place of ROUND... now it works perfectly.

Wow! Thanks @A. Scott McCulloch ! That makes a lot of sense. I'll give it a try.

I just got to try the ROUNDDOWN in my agenda and it totally worked! Thank you SO MUCH for posting that suggestion!
Help Article Resources
Categories
Check out the Formula Handbook template!