If statement and countif statement in an array

**jntydeman** · 01-21-2011, 12:57 PM

I am creating a shift schedule for my company. I would like to create a formula (using an array, I believe, is the only way to do this...but if there is another way I would love to hear it!) which will determine if the employee is entitled to a one hour lunch, based on the number of hours worked that day. The problem I am running in to is that the standard shifts vary (either 8 hour shifts or 10 hour shifts).

Basically, I want colum AJ "Lunch Hour" to know if the employee took a one hour lunch that day, based on the total number of hours they worked (AH) and their standard shift (AI). I want it to show a "1" in AJ when the standard shift is 10 hours and the person worked 5 or more hours (you don't get a lunch if you work a half day) OR if the standard shift is 8 hours, then they only get the one hour lunch when they work 4 or more hours.

The way I have the AJ colum calculating now, it cannot distinguish between people who worked a full day and people who worked a half day.

I am using Excel 2007, but I have uploaded a 2003 compatible file to this post.
Thanks!!

**DonkeyOte** · 01-21-2011, 06:46 PM

Perhaps:

AJ3:
=IF($B3="","",COUNTIF($AH3, ">="&$AI3/2))
copied down

Incidentally - from what I can tell

AH4:
=IF($B3="","",COUNTA($C14:$AF4))
copied down

**jntydeman** · 01-25-2011, 12:40 PM

DonkeyOte,
Thanks for your help! The code you have provided for the AJ column is working, sort of

. It still can't tell the difference when someone takes a half day. For example, if the number in the AH cell is a 4 (for a person who normally works an 8 hour shift), it still enters a 1 in the AJ column. I have changed the shift in row 14 to give this example.
Perhaps this problem is related to the formula you provided for the AH column?? When I enter that code, I get some odd numbers. I have re-uploaded my file with these changes for you to see.
Thanks again!

**DonkeyOte** · 01-26-2011, 08:12 AM

Originally Posted by jntydeman

Perhaps this problem is related to the formula you provided for the AH column?? When I enter that code, I get some odd numbers

Typo on my part re: above (misplaced number 1 in range reference)

Given your latest sample:

AH3:
=IF($B3="","",COUNTA($C3:$AF3))
copied down

Originally Posted by jntydeman

if the number in the AH cell is a 4 (for a person who normally works an 8 hour shift), it still enters a 1 in the AJ column.

to be clear the formulae in AH were wrong but regards the above based on your OP (below) 4 hours for an 8 hour shift would still warrant a lunch break:

Originally Posted by jntydeman-post1

if the standard shift is 8 hours, then they only get the one hour lunch when they work 4 or more hours.

If you're saying now that lunch is included only where ee works greater than 1/2 shift as opposed to greater than or equal to 1/2 shift you should adjust the operator accordingly in the AJ formula: from >= to >

**jntydeman** · 01-26-2011, 11:35 AM

Originally Posted by DonkeyOte

Typo on my part re: above (misplaced number 1 in range reference)

Given your latest sample:

AH3:
=IF($B3="","",COUNTA($C3:$AF3))
copied down

to be clear the formulae in AH were wrong but regards the above based on your OP (below) 4 hours for an 8 hour shift would still warrant a lunch break:

If you're saying now that lunch is included only where ee works greater than 1/2 shift as opposed to greater than or equal to 1/2 shift you should adjust the operator accordingly in the AJ formula: from >= to >

------------------------------------------------------------------------------------------------------------------------------
This is now working perfectly as intended! Thank you so much for your assistance!