Simple detailed agenda in Excel

Posted On 2020-03-03

I’ve been doing some detailed agendas over the past weeks for events and workshops and so I obviously made an Excel file to help me out. Figured I’d share it with whoever might have use for it.

---

How to use it?

This is what is looks like at a glance. Simple and clean, right!

I’ve locked the cells so that you can only edit these ones. You have to set the first start time and the end time, after that it’ll pick the end time from the line above as the start time for each line and it calculates the duration by itself

Let’s fill in some examples down here. As you can see, the next row will always suggest the end time for the row above.

---

How it works

The Start is simple calculated by looking at the row above. If it’s blank, this cell to will be blank. Otherwise it provides what’s in that cell.

The formula is like this: =IF(ISBLANK(B2);””;B2)

Next we have the duration. This is a bit longer and I’ll break it down for you underneath the image. The formula looks like this: =IF(OR(ISBLANK([@Start]);ISBLANK([@End]));””;IF([@Start]>[@End];”Extend end”;B4-A4))

If we break down the formula into 3 pieces it looks something like this.
The red line will check if either Start or End is blank. If either one is blank, the duration is not calculated at all. It just spits out “”.
The orange part will be calculated if the red part is false, meaning it will only be calculated if there is in fact a Start and an End. The Orange part checks if the Start is bigger than the End. The way Excel calculates time, it would mean that the start time is after the End time and since that just won’t do, we’ll provide the text “Extend end”.
If the start time is indeed before the end time, all is good and we’ll just subtract the Start from the End.

Finally I’ve looked the sheet. I’ve deselected “Protect” from the cells where you are allowed to edit. It has no password, so if you want to make changes you can simply unprotect the sheet.