Count with conditions?

[Geo]

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.

 

[Joel]

Excel stroes a day aas 1 and an hour as 1/24. To convert hours to whole
numbers simply multiply by 24. You may want to use the round function after
multiply to get an integer number of hours. You could also use the FHOUR()
function to get the number of hours insted of multiplying by 24. Hours will
alwasy give you an integer response but will only give you a number from 0 to
23.

 

[NDBC]

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,sum(b1:b7)>=2),1,"")

Assuming a seven day week as well.

 

[David Biddulph]

What does the FHOUR function do? Which version of Excel contains FHOUR?

The HOUR function would presumably give the same result as INT(MOD(A1,1)*24)

 

[Geo]

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

 

[David Biddulph]

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,""))

 

[Geo]

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!!

 

[NDBC]

=IF(AND(MAX(B1:B7)>=1,SUM(B1:B7)>=2),2,IF(OR(Max(B1: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]

I shall try it
Thank you NDBC

=IF(AND(MAX(B1:B7)>=1,SUM(B1:B7)>=2),2,IF(OR(Max(B1: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.

 

[NDBC]

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,""))

 

[Geo]

Yes NDBC that did the trick. It works a treat!!
Thank you very much.