Question: I’m trying to create a formula in Microsoft Excel 2003/XP/2000/97. I have a situation where the user in a daycare center needs to count the number of children that will be in attendance at lunch time. Here is what the spreadsheet looks like:
| Column A | Column B | Column C |
|---|---|---|
| Name: | Monday In | Monday Out |
| Joe | 8:00 AM | 1:00 PM |
| Mary | 9:30 AM | 12:00 PM |
| Cindy | 7:00 AM | 11:30 PM |
| Sam | 8:00 AM | 5:00 PM |
| Isaac | 12:00 PM | 6:00 PM |
| Daylon | 7:30 AM | 12:00 PM |
| Robert | 9:00 AM | 6:00 PM |
| Alliah | 11:00 AM | 4:00 PM |
The formula needs to count only those kids where the value in column B is less than 12:00PM and the value in column C is greater than 12:00PM. Lunch is served at 12 noon so only 5 of the 8 listed above would remain to have lunch.
How can I do this?
Answer: This can be done in Excel with an array formula.
Let's look at an example.
In cell A11, we've created the following array formula:
=SUM(((HOUR(B2:B9)*60+MINUTE(B2:B9))<720)*((HOUR(C2:C9)*60+MINUTE(C2:C9))>720))
When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:
{=SUM(((HOUR(B2:B9)*60+MINUTE(B2:B9))<720)*((HOUR(C2:C9)*60+MINUTE(C2:C9))>720))}
What this formula does is convert the time values in column B and column C into minutes, then count only those kids where the time value in column B is less than 720 minutes (ie: 12:00PM) and the time value in column C is greater than 720 minutes (ie: 12:00PM).
To explain further, 12:00PM = (12 hours * 60 minutes) which equals 720 minutes.
