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.

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.

"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.

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.

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)
--
David Biddulph

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

"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.

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.

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.

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.

=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.

I shall try it
Thank you NDBC

"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.

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.

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

"NDBC" wrote:

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.