MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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? :