Multiple Functions
 

I already asked a question similar to this one, but I thought I should try
and be more specific. This is what I am trying to do, I want one cell to be
able to calculate all of these times together so I can keep a running total
for the year. I need that cell to calculate 8 hours a month of comp time and
we also get 1 hour for every 8 hours of standby. I have one reply to my first
message stating that it would probably be easier to use 2 columns, which I
will probably have to do if I can't get any other suggestions for my problem.
Thanks again for any help that may be provided!
Kevin.

Hi

When you have dates in column A, and standby times as numbers in column B,
with data p.e. in range A2:B1000, then the summary standby time for p.e.
February of current year will be:
=SUMPRODUCT(--(Year($A$2:$A$1000)=2500),--MONTH($A$2:$A$1000)=2),$B$2:$B$100
0)
formatted as general or number.
The number of 8-hour time intervals will be the formula above divided by 8.

When standby times are entered in time format, like 4:00 or 8:00, then
formula for summary time is same, but you have to format the cell with
formula as Custom "[h]" or "[h]:mm". To calculate the number of 8-hour time
intervals, multiply the formula with 24*60, and divide by 8.

When the workbook contains dates from one single year, then you can omit the
first comparision, the one with YEAR function, in formula(s). You also can
then use SUMIF function instead of SUMPRODUCT.

When calculating same figures for year, you omit the month comparision in
SUMPRODUCT formula. You also can use SUMIF instead of sumproduct. When the
workbook contains data from single year only, you can summarize standbay
times simply with formula
=SUM(B:B)


Arvi Laanemets


"Kevin" wrote in message
...
I already asked a question similar to this one, but I thought I should try
and be more specific. This is what I am trying to do, I want one cell to
be
able to calculate all of these times together so I can keep a running
total
for the year. I need that cell to calculate 8 hours a month of comp time
and
we also get 1 hour for every 8 hours of standby. I have one reply to my
first
message stating that it would probably be easier to use 2 columns, which I
will probably have to do if I can't get any other suggestions for my
problem.
Thanks again for any help that may be provided!
Kevin.

LOL


=SUMPRODUCT(--(Year($A$2:$A$1000)=2500),--MONTH($A$2:$A$1000)=2),$B$2:$B$100
0)
must be of-course

=SUMPRODUCT(--(Year($A$2:$A$1000)=2005),--MONTH($A$2:$A$1000)=2),$B$2:$B$100
0)


Arvi Laanemets

Did you try that other suggested formula?

Kevin wrote:

I already asked a question similar to this one, but I thought I should try
and be more specific. This is what I am trying to do, I want one cell to be
able to calculate all of these times together so I can keep a running total
for the year. I need that cell to calculate 8 hours a month of comp time and
we also get 1 hour for every 8 hours of standby. I have one reply to my first
message stating that it would probably be easier to use 2 columns, which I
will probably have to do if I can't get any other suggestions for my problem.
Thanks again for any help that may be provided!
Kevin.

--

Dave Peterson

Thanks for the help. What I did was Sum 1-31 cells and then divided by 8 and
then ensured my cell for stndby hours also calculated using that cell. Seems
to work...

"Kevin" wrote:

I already asked a question similar to this one, but I thought I should try
and be more specific. This is what I am trying to do, I want one cell to be
able to calculate all of these times together so I can keep a running total
for the year. I need that cell to calculate 8 hours a month of comp time and
we also get 1 hour for every 8 hours of standby. I have one reply to my first
message stating that it would probably be easier to use 2 columns, which I
will probably have to do if I can't get any other suggestions for my problem.
Thanks again for any help that may be provided!
Kevin.