Sorry NDBC. I get an error message saying the formula you typed contains an
error?

"NDBC" wrote:

This will only work if the dates in column a are consecutive (ie. all dates
are listed, even dates with 0 hours)

=if(and(max(b1:b7)=1,sum(b1:b7)=2),2,or(b7=1,su m(b1:b7)=2),1,"")

Assuming a seven day week as well.


"Geo" wrote:

How can I put the following as a formula:

1. 1 hour or greater to count as "1"
2. or a cumulative total of 2 hours (or more) in any seven consecutive days
to count as "1"
3. If the 1 hour and the 2 hours are exceeded within a consecutive seven day
period should count as "2"

The table is set out as follows:
cols: A= Date
B= Hours

Cells: "P5"= a consecutive 7 day period ("G5" and "H5"= dates for a rolling
last 7 days).

I have tried 'COUNT' but having trouble with it.