Agenda with start and end time per presenter

Options

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?


Best Answer

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    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

Answers

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Leann Gibson ✭✭✭✭✭
    Options

    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
    Heather Duff ✭✭✭✭✭✭
    Options

    @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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!