Here is an example of how you can do what you're trying to do. You can
adjust to make it work for your worksheet's layout.
Assumption 1: Your dates are in the first row, starting at column 2
Assumption 2: Each row below row 1 has a person's name in column 1
Assumption 3: Each cell in a person's row that is underneath a date cell in
row 1 is either blank (not a vacation day) or has a 1 in it (is a vacation
day).
Let's do February as an example. Columns B:AC will have the Feb dates in
Row 1. Label column AD as "Total Vacation Days", column AE as "Vacation Days
Taken" and column AF as "Vacation Days Planned". Then the following formulas
will add up the various parts.
For the first person (in row 2)...
Formula for total vacation days (in cell AD2):
=SUMIF($B2:$AC2,1)
Formula for vacation days taken (in cell AE2):
=SUM(IF($B2:$AC2=1,IF($B$1:$AC$1<TODAY(),1,0),0))
Formula for vacation days planned (in cell AF2):
=SUM(IF($B2:$AC2=1,IF($B$1:$AC$1>=TODAY(),1,0),0))
The last two formulas are ARRAY formulas, so when you type them in, you must
hold down SHIFT and CTRL keys while hitting ENTER, or they won't work. You
know you have done that right when you see the "{" and "}" around your
formulas after you enter them.
You can select all the "person" rows from 2 down, columns AD to AE, and do a
fill down and the formulas should then work in each row.
Columns AE and AF should add up to the total in AD. Here's a very short
example. It assumes today is 2-16-09.
Date 2-13 2-14 2-15 2-16 2-17 2-18 Total Taken
Planned
John Doe 1 1 1 1 1 5
3 2
HTH,
Eric
