Calculate time by adding hours and minutes

Pam Grant

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.

Andrew Stewart

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

A. Scott McCulloch

@Pam Grant

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.

Andrew Stewart

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

Pam Grant

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. :(

Pam Grant

Thank you @Andrew Stewart !

Your solution worked perfectly.

Pam Grant

Hi @Andrew Stewart

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?

A. Scott McCulloch

@Pam Grant

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.

Pam Grant

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

Pam Grant

@A. Scott McCulloch

I just got to try the ROUNDDOWN in my agenda and it totally worked! Thank you SO MUCH for posting that suggestion!