Calculating Holiday Hours PLEASE HELP!
Posted by Karen on February 01, 2002 6:30 AM
I have a timesheet that calculates the hours correctly,
however I am having trouble getting it to calculate the
holiday hours. I am working in cell Q32 and my formula
is "SUMIF(T13:T19,"HOLIDAY",H13:H19)". Cells T13:T19
are where they write in Holiday for holiday pay. Cells
H13:H19 are the hours worked during that week. The
problem I'm having is, if an individual works 10 hours
on a holiday, they will still only get 8 hours of
holiday pay. How do I create a formula that will give
me only 8 hours if they worked the holiday instead of
10. Or if the individual works 7 hours instead of 8,
it should only read 7. I tried doing an IF statement,
but it kept giving me a #VALUE error message. Any help
anyone can give me on this will be very much
appreciated.
Thanks in advance,
Karen
Posted by Aladin Akyurek on February 01, 2002 6:38 AM
Re: Calculating Holiday Hours
Does the timesheet belong to just one person?
Posted by Benny on February 01, 2002 6:43 AM
*****
You can enter an ARRAY formula like the following:
=SUM(IF(H9:H15="Holiday",IF(I9:I15>8,8,I9:I15)),0)
Instead of just hitting enter after typing in the formula, do a CTRL+SHIFT+ENTER to create an array formula.
Posted by Benny on February 01, 2002 6:44 AM
Monor update to suite your spreadsheet
Corrected range values for your worksheet
*****
You can enter an ARRAY formula like the following:
=SUM(IF(T13:T19="Holiday",IF(H13:H19>8,8,H13:H19)),0)
Instead of just hitting enter after typing in the formula, do a CTRL+SHIFT+ENTER to create an array formula.
Posted by Karen on February 01, 2002 11:27 AM
Re: Calculating Holiday Hours
Yes, there is one timesheet per individual, but 17 timesheets per workbook.
Posted by Karen on February 01, 2002 1:04 PM
Benny, tried the array but it didn't work.
You can enter an ARRAY formula like the following: =SUM(IF(H9:H15="Holiday",IF(I9:I15>8,8,I9:I15)),0) Instead of just hitting enter after typing in the formula, do a CTRL+SHIFT+ENTER to create an array formula. : I have a timesheet that calculates the hours correctly,
Posted by Aladin Akyurek on February 01, 2002 1:42 PM
Re: Calculating Holiday Hours
You probably want to have a total per individual including "normalized" holiday hours:
In Q32 array-enter: =SUM(IF(T3:T19="Holiday",IF(H3:H19 > 8,8,H3:H19)),IF(T3:T19 <> "Holiday",H3:H19))
This incorporates Benny's for range corrected suggestion.
You need to hit control+shift+enter at the same time instead of just enter.
========== : Does the timesheet belong to just one person? :
