Timed Agenda

alwayslearning

Meeting Agenda (Agenda Timeline Calculator)

Does anyone have a template or a meeting agenda tool that calculates start and end times for each speaker? My organization holds a 2-day meeting that always gets off schedule. We'd like to be able to enter a meeting start time, then add each speaker's length (minutes) and have the Agenda's time schedule automatically update each speakers' start and end times. A bonus would be to be able to drag a speaker's row to a different time slot and have the timeline automatically update.

Any suggestions would be greatly appreciated.

HeatherD.

Hi, @alwayslearning !

Smartsheet currently does not have time tracking features; however, @Paul Newcome has created this post with VERY helpful information regarding using time in Smartsheet.

I previously created an agenda similar to what you're describing, though moving the rows around requires drag-filling the formulas in the time columns in order to realign things. It was somewhat complex to set up, but definitely functional.

The cells in green are cells in which you enter data. The Break column can be used to insert a gap between sessions. (If you don't need a gap, you can either edit the formulas to omit the Break, or set the Break to 0 for each row and hide the column.) These formulas account for the sessions to be less than 3 hours long. If they will be longer than that, additional IF statements will have to be added to the End Hour and End Minutes formulas.

Here are the formulas in each column:

Start Hour (formula NOT inserted in row 1; cannot be column formula) - enter in row 2 and drag-fill down: =IF([End Minutes]1 + [Break Minutes]1 < 60, [End Hour]1, [End Hour]1 + 1)

Start Minutes (formula NOT inserted in row 1; cannot be column formula) - enter in row 2 and drag-fill down: =IF([End Minutes]1 + [Break Minutes]1 < 60, [End Minutes]1 + [Break Minutes]1, [End Minutes]1 + [Break Minutes]1 - 60)

End Hour (column formula): =IF([Length Minutes]@row + [Start Minutes]@row < 60, [Start Hour]@row, IF([Length Minutes]@row + [Start Minutes]@row < 120, [Start Hour]@row + 1, [Start Hour]@row + 2))

End Minutes (column formula): =IF([Length Minutes]@row + [Start Minutes]@row < 60, [Start Minutes]@row + [Length Minutes]@row, IF([Length Minutes]@row + [Start Minutes]@row < 120, [Start Minutes]@row + [Length Minutes]@row - 60, IF([Length Minutes]@row + [Start Minutes]@row < 180, [Start Minutes]@row + [Length Minutes]@row - 120)))

Start Time (column formula): =IF([Start Hour]@row < 13, [Start Hour]@row, [Start Hour]@row - 12) + ":" + IF([Start Minutes]@row < 10, "0", "") + [Start Minutes]@row + IF([Start Hour]@row < 12, " AM", " PM")

End Time (column formula): =IF([End Hour]@row < 13, [End Hour]@row, [End Hour]@row - 12) + ":" + IF([End Minutes]@row < 10, "0", "") + [End Minutes]@row + IF([End Hour]@row < 12, " AM", " PM")

Hope this helps!

Best,

Heather

HeatherD.

Hi, @alwayslearning !

Smartsheet currently does not have time tracking features; however, @Paul Newcome has created this post with VERY helpful information regarding using time in Smartsheet.

I previously created an agenda similar to what you're describing, though moving the rows around requires drag-filling the formulas in the time columns in order to realign things. It was somewhat complex to set up, but definitely functional.

The cells in green are cells in which you enter data. The Break column can be used to insert a gap between sessions. (If you don't need a gap, you can either edit the formulas to omit the Break, or set the Break to 0 for each row and hide the column.) These formulas account for the sessions to be less than 3 hours long. If they will be longer than that, additional IF statements will have to be added to the End Hour and End Minutes formulas.

Here are the formulas in each column:

Start Hour (formula NOT inserted in row 1; cannot be column formula) - enter in row 2 and drag-fill down: =IF([End Minutes]1 + [Break Minutes]1 < 60, [End Hour]1, [End Hour]1 + 1)

Start Minutes (formula NOT inserted in row 1; cannot be column formula) - enter in row 2 and drag-fill down: =IF([End Minutes]1 + [Break Minutes]1 < 60, [End Minutes]1 + [Break Minutes]1, [End Minutes]1 + [Break Minutes]1 - 60)

End Hour (column formula): =IF([Length Minutes]@row + [Start Minutes]@row < 60, [Start Hour]@row, IF([Length Minutes]@row + [Start Minutes]@row < 120, [Start Hour]@row + 1, [Start Hour]@row + 2))

End Minutes (column formula): =IF([Length Minutes]@row + [Start Minutes]@row < 60, [Start Minutes]@row + [Length Minutes]@row, IF([Length Minutes]@row + [Start Minutes]@row < 120, [Start Minutes]@row + [Length Minutes]@row - 60, IF([Length Minutes]@row + [Start Minutes]@row < 180, [Start Minutes]@row + [Length Minutes]@row - 120)))

Start Time (column formula): =IF([Start Hour]@row < 13, [Start Hour]@row, [Start Hour]@row - 12) + ":" + IF([Start Minutes]@row < 10, "0", "") + [Start Minutes]@row + IF([Start Hour]@row < 12, " AM", " PM")

End Time (column formula): =IF([End Hour]@row < 13, [End Hour]@row, [End Hour]@row - 12) + ":" + IF([End Minutes]@row < 10, "0", "") + [End Minutes]@row + IF([End Hour]@row < 12, " AM", " PM")

Hope this helps!

Best,

Heather

alwayslearning

Hi @HeatherD.,

Thank you so much! I struggle with formulas. Thank you so much for sharing them! I'll be working on this over the next couple of weeks!

Nancy

HeatherD.

@alwayslearning Happy to help! Have a great week.