formulas

[knowitallnot]

i have a rota for staff working a pattern of shifts from 9.00 - 20.00
the rota is set up for 7 weeks and i would like to create a formula to calculate lunches (i.e. if starting at 9.00, they have a 12.00 lunch and if starting at 12.00 they have a 15.00 lunch and so on)
presume this is possible but can't get the sumif formula to work

i would also like it to work out an equal split of lunches to ensure adequate cover (ie if 4 people starting at 9 - lunches are evenly split: 2 people on 12.00 lunch and 2 people on 1.00 lunch) but don't know where to start with this one

please help

[mmwilcox]

You've had some looks at your post but no replies. I'll see if I can help.

To add time, you have enter your math components in terms of fractions of a day. Each day = (24 hours * 60 minutes) or 1440 minutes. To add three minutes, you would add 3/ 1440 or 0.00208. To add 3 hours, you would add 180/ 1440 or 0.125. Format cells A1 and B1 for time and enter 9:00 into A1. In B1, type: +A1 + 0.125. Hit enter and it should show 12:00.

As far as splitting the lunches; this gets complex in a hurry. To calculate this for each person, each has to have 1) a count of how many total are on that shift and 2) where is each compared to the others. If there are 6 of us starting at 9:00, and I'm the 1st in the list, I get lunch at 12:00. If I'm the 4th, I get lunch at 1:00. I can get the total count using: =COUNTIF(A1:A7,"9:00"). But getting the relative position of any one employee compared to the rest on the same shift gets sticky.

This gets additionally complex since this affects people on the 10:00 shift as well, since some of them will be on the 1:00 lunch. But if you have enough 9:00 people on a 1:00 lunch, then do you move the 10:00 people to 2:00?

Hopefully, that gives you a start.

Good luck.

Matt

[knowitallnot]

Thanks very much - this has worked

Jackie