Time Window Generator for Schedules

As I was given the task of arranging an academic meeting with presentations, aside from all the work that needs to be put into this program, I was wondering how to automate the time slots to be calculated according to the changing presentation times that would be given. It would be annoying to write every time window one by one and then when something changes, somebody whose time is not valuable enough (like a PhD student) would have to write every other time slot again and again.

I searched for something made for that but couldn’t find a free and easy solution.

So I tried some functions in Google Sheets and it worked perfectly!

This is how it looks like without showing the schedule itself.

This way it is possible to just enter the beginning time in hh:mm format (like 08:30), then give the Time Window column a number for the minutes (60 for 1 hour) and you have a generated Time Range of that particular part! It is now much easier to change the time given to a presentation, add new slots, remove rows and so on.

The link is here to use it yourself by downloading or making a copy on Google Sheets.

How to use it can be tricky if you’re not used to Excel or Google Sheets, but I wrote some instructions that can help.

Things that can be tricky

1. There can be some parts that are only text and not a presentation, so those parts just get a zero in the Time Window column.
2. Adding and removing rows would disrupt the functions below, so selecting the related cells and then dragging the little blue box will fix the calculations.
3. The hours and minutes may look like hh:mm:ss (like 08:30:00), to fix that on Google Sheets you can choose those columns and click Format> Number > More Formats > More date and time formats > (then choose the one with 13:30 or some similar one).

What do the functions do?

Maybe you’re wondering how this works, so here I explain that.

(it might not be the best, but it works for me!)

1. The HOUR(..) function is to get the hour part from the clock (getting the 8 out of 8:30)

2. The MINUTE(..) function is to get the minutes part from the clock (getting the 30 out of 8:30)

3. Then the TIME(hour; minute; second) function takes hours and minutes, and calculates the excessive minutes into hours. Here we add the minutes from Time Window into the minutes from the beginning clock (which is the End result of one row above actually).

4. After writing these functions together in a cell for one row, I just selected these 2 cells with beginning and ending times and then dragged the little blue box on right corner which helps to copy the function according to its own cell number, and that copied everything until the end.

5. These gave me the Beginning and Ending times but I needed something like a range (like 08:30–09:00) and for that I used TEXT(..) function to turn the numbers into text, and then CONCATENATE(..) with a “-” in between to bring them together.

Now everything changes according to what I write in Time Window.

I hope this is helpful for someone that struggles to get things done in the shortest time possible. Automate the boring stuff!

I’m very curious about what could have been better and If there are any other better ways to do this. Please share your thoughts.

Thank you for reading!