I have the honour of being an associate lecturer at the LCC, and a job I had to do yesterday was work out one-to-one tutorial slots for my class of 32 students. We have a total of six hours for these, and I needed to divide the time up equally between them and generate a list of times for the appointments. Rather than mucking about with bits of paper or manual calculation, here’s how I did it in Numbers; it’s likely Excel is similar,* but I haven’t tried it. In the screenshots for the guide below, I’ve hidden the first couple of columns, as they contain sensitive data – the one name showing in the formula is fake – but otherwise this is live.
01: In cell C2, I enter the start time, 13:00, and set the format for columns C and D to time. Then click in cell D2 and type ‘=’ to switch to formula mode, click in C2 to say you want to use the value from whatever’s there, then type ‘+’ because you want to add time to it, then start typing ‘DURATION’.
02: Because we know we have six hours and 32 pupils, I can have Numbers do the maths for me rather than having to work out how many minutes that is for each, dealing with fractional minutes and rounding errors. Click the Hours lozenge and type ‘(6/32)’ so the calculation is done for you – Numbers replaces the slash with a proper division symbol 😍 – and leave the other lozenges. You could replace them all with ‘0’, but there’s no need to.
03: Now since we know when the first appointment ends, we know when the second starts. Click in cell C3 and type ‘=’ and then just click in D2. (You could get fancy and introduce buffers here with additional duration values, but I didn’t need to.)
04: Now click D2 and hover over the bottom edge of the cell. Note the little yellow blob that appears halfway along, then drag this down the full length of your To column. Release, and it will populate them all with duration values based on the value in the cell immediately to their left, in column C; they’ll all just be duration values at this stage rather than times because they don’t have a start time to calculate from, but we’re about to change that.
05: Now do the same thing in cell C3, which will fill down the formula that says ‘for this cell’s value, look at the cell one to the right and one up’. This will give the formulas you copied into column D a figure to calculate from, and so those time values will now complete as well.
There you go. Quick, easy, exportable. You could probably get even smarter with the ‘(6÷32)’ bit if you wanted to keep thing more dynamic, holding the value for the number of hours in another cell and referencing it, and calculating the 32 automatically with a Count formula, but this was enough for me.
* Numbers’ formulae operators tend to work almost identically to Excel’s, which is handy when trying to work out how to do something through Googling, in part because there’s far more material for Excel, but also because dear god, Apple, calling your spreadsheet app Numbers is not SEO-friendly.