Agenda with start and end time per presenter

Leann Gibson

Trying to create a template for Agenda's with a start and end time based on the minutes needed per topic. Then I want the next topic to start when the first ended and so on. I can do this in Excel, having trouble in SmartSheet.

Any suggestions?

Heather Duff

Hi @Leann Gibson ,

I waited to respond to to this in hopes that someone would have a clean answer (that I could steal!); however, it doesn't look like there is one.

I have workarounds that I have created for this, but they're a bit clunky. Instead of having a column with HH:MM AM/PM all in one for each time, I have broken it down to multiple columns (see screenshot).

From there, you can set up formulas in the End columns to add the duration in minutes to the Start Minutes. But then, if the minutes added go over an hour, your formula in the End Hour needs to know what to do with it. And in the hours columns, unless you're using military time, you have to account for AM/PM. It ends up being fairly complicated, but functioning nonetheless.

See the screenshots below and the formulas I've entered:

End Hour column:

=IF(IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Hour]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Hour]@row + INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60))) > 12, IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Hour]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Hour]@row + INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60))) - 12, IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Hour]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Hour]@row + INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60))))

End Minutes column:

=IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Minutes]@row + [Duration (Minutes)]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Minutes]@row + [Duration (Minutes)]@row - 60 * INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60)))

End AM/PM column: *I'm sure there's a way to simplify this formula, but so far I have not found it.

=IF(OR([End Hour]@row = [Start Hour]@row, (AND([End Hour]@row < 12, [End Hour]@row >= [Start Hour]@row)), (AND([Start Hour]@row = 12, [End Hour]@row < [Start Hour]@row))), [Start AM/PM]@row, IF(OR(AND([Start AM/PM]@row = "AM", [Start Hour]@row <> 12, [Start Hour]@row > [End Hour]@row), (AND([End Hour]@row = 12, [End Hour]@row > [Start Hour]@row))), "PM", IF(OR(AND([Start AM/PM]@row = "PM", [Start Hour]@row <> 12, [Start Hour]@row > [End Hour]@row), (AND([End Hour]@row = 12, [End Hour]@row > [Start Hour]@row))), "AM", "")))

Then, in the start time columns for rows 2 and beyond, I linked to the end time for the previous row. For example, in row 2 start hour, the formula is =[End Hour]1; the start minutes cell is =[End Minutes]1, and the AM/PM is =[End AM/PM]1.

Hope this helps (and isn't too complicated)!

Best,

Heather

Heather Duff

Hi @Leann Gibson ,

I waited to respond to to this in hopes that someone would have a clean answer (that I could steal!); however, it doesn't look like there is one.

I have workarounds that I have created for this, but they're a bit clunky. Instead of having a column with HH:MM AM/PM all in one for each time, I have broken it down to multiple columns (see screenshot).

From there, you can set up formulas in the End columns to add the duration in minutes to the Start Minutes. But then, if the minutes added go over an hour, your formula in the End Hour needs to know what to do with it. And in the hours columns, unless you're using military time, you have to account for AM/PM. It ends up being fairly complicated, but functioning nonetheless.

See the screenshots below and the formulas I've entered:

End Hour column:

=IF(IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Hour]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Hour]@row + INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60))) > 12, IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Hour]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Hour]@row + INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60))) - 12, IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Hour]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Hour]@row + INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60))))

End Minutes column:

=IF([Start Minutes]@row + [Duration (Minutes)]@row < 60, [Start Minutes]@row + [Duration (Minutes)]@row, IF([Start Minutes]@row + [Duration (Minutes)]@row >= 60, [Start Minutes]@row + [Duration (Minutes)]@row - 60 * INT(([Start Minutes]@row + [Duration (Minutes)]@row) / 60)))

End AM/PM column: *I'm sure there's a way to simplify this formula, but so far I have not found it.

=IF(OR([End Hour]@row = [Start Hour]@row, (AND([End Hour]@row < 12, [End Hour]@row >= [Start Hour]@row)), (AND([Start Hour]@row = 12, [End Hour]@row < [Start Hour]@row))), [Start AM/PM]@row, IF(OR(AND([Start AM/PM]@row = "AM", [Start Hour]@row <> 12, [Start Hour]@row > [End Hour]@row), (AND([End Hour]@row = 12, [End Hour]@row > [Start Hour]@row))), "PM", IF(OR(AND([Start AM/PM]@row = "PM", [Start Hour]@row <> 12, [Start Hour]@row > [End Hour]@row), (AND([End Hour]@row = 12, [End Hour]@row > [Start Hour]@row))), "AM", "")))

Then, in the start time columns for rows 2 and beyond, I linked to the end time for the previous row. For example, in row 2 start hour, the formula is =[End Hour]1; the start minutes cell is =[End Minutes]1, and the AM/PM is =[End AM/PM]1.

Hope this helps (and isn't too complicated)!

Best,

Heather

Leann Gibson

This is help and I submitted an enhancement request to SmartSheet. I am hoping they create a simple way to do this. It is so easy in Excel.

Heather Duff

@Leann Gibson Happy to help! Yes - It would be great to have time functionality. I was seriously hoping I was just missing a time feature, but it doesn't seem to be the case. Hopefully they'll create it soon! Have a great weekend.