Just hqad another thought. I've been assuming you've been putting in hours in
general format, not time format. Say for 1.5 hours do you put 1.5 hours in
column B or 1:30. If you are putting 1:30 then the formula I gave you won't
work.

try this

=IF(AND(MAX(B1:B7)*24=1,SUM(B1:B7)*24=2),2,IF(OR (Max(B1:B7)*24=1,SUM(B1:B7)*24=2),1,""))



"NDBC" wrote:

=IF(AND(MAX(B1:B7)=1,SUM(B1:B7)=2),2,IF(OR(Max(B 1:B7)=1,SUM(B1:B7)=2),1,""))

Put this in cell C7 (or whatever column you prefer) and copy/drag it down as
many rows as you have dates for. Again, this does not check any dates. It
relys on every date having a row, even weekends. If you are only putting in
dates that have times against them then this will not work.



"Geo" wrote:

Thanks Dave, but still not working, I'll try explaining agian.
What I'm after is to count between any consecutive 7 day period any point
the time goes above 1 hour count as ("1"). Or if the cumulative total goes
over 2 hours count as ("1"). Hopefully this is made it clearer!!

"David Biddulph" wrote:

I think the intention may have been
=IF(AND(MAX(B1:B7)=1,SUM(B1:B7)=2),2,IF(OR(B7=1 ,SUM(B1:B7)=2),1,""))
--
David Biddulph

Geo wrote:
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.