In ANY 7 consecutive boxes in a row, ensure there are at-least 2 boxes containing words x.

Question

Level 1

4 points

In ANY 7 consecutive boxes in a row, ensure there are at-least 2 boxes containing words x.

Basically its a 31x10 table.

Each column represents a day of the month.

Each row is an employee.

I need an equation to work out, for example, that in ANY 7 consecutive days, there are at-least 2 boxes containing the words RDO.

Then, in ANY 7 consecutive days, there are no more than 60 hours worked.

The boxes are filled out like this.

5.5(8.0) or RDO or its just blank meaning the day has not been rostered yet

Where the 5.5 is time doing a specific task

and the 8.0 is the total time at work that day.

and RDO is....well a day off work.

If this is ever exceeded, highlight a separate box in a separate table red, and write which box is the problem one.

Does this make sence.

It will continue to if 60hrs worked, next 7 days less than x. but thats for another day.

Cheers

Posted on Oct 20, 2024 7:07 AM

Reply

Answer 1

Camelot

Level 9

55,101 points

Oct 21, 2024 10:27 AM in response to callumgualdi

This is going to be tricky.

Some of it I can see a solution to, but some is complicated by what you're saying.

For example, I read:

5.5(8.0) or RDO or its just blank meaning the day has not been rostered yet

As saying a cell could contain the text "5.5(8.0)", and that the sheet has to somehow work out that the '5.5' represents one value, and the '(8.0)' represents something different.

That's hugely complicated for a spreadsheet - you can't just add "5.5(8.0)" to another "5.5(8.0)" and get any meaningful result. 5.5+5.5? easy. 8.0+8.0? easy, too. But "take the '5.5' from the string '5.5(8.0)' and add it to the '4.5' from '4.5(8.0)'"? I can't even begin to wrap my head around.

So before we go too far down this path, are you sure the format you're using is the best one?

Is it possible, for example, to have different columns for the 'task' and 'total hours' time each day? or maybe a separate table of 'total hours' from 'task hours', which Numbers could cross-reference?

Then you have the rolling schedule. Checking a set 7-day schedule is easy. Checking *any* consecutive 7 days in the month is harder. What about rollovers between months? What happens if there's an RDO in the last day of one month and the first day in the next month? does that count?

Reply