Hi I'm using the attendance tracker, with Excel 2003.

When my people turn in their vacation schedules for the year, I enter them
all on the tracker.

I need to calculate the number of vacation days taken vs. the number of days
planned.

How do I do that?

On Feb 16, 9:04*am,
icrosoft.com wrote:
Hi I'm using the attendance tracker, with Excel 2003.

When my people turn in their vacation schedules for the year, I enter them
all on the tracker.

I need to calculate the number of vacation days taken vs. the number of days
planned.

How do I do that?

Subtract the two cells?
example:
=B1-A1

No, I guess I ddn't explain properly.

The attendance tracker has a cell for each date across the top (i.e. 2/16,
2/17, 2/18 etc.). If today is 2/16, I do not want to count the days in the
2/17 or 2/18 column as a day taken, but rather as a day planned.


"CurlyDave" wrote:

On Feb 16, 9:04 am,
icrosoft.com wrote:
Hi I'm using the attendance tracker, with Excel 2003.

When my people turn in their vacation schedules for the year, I enter them
all on the tracker.

I need to calculate the number of vacation days taken vs. the number of days
planned.

How do I do that?

Subtract the two cells?
example:
=B1-A1

I'm guessing a need a conditional sum, but not sure how to evaluate each
column (i.e. 2/16, 2/17, 2/18) to determine if that date is less than the
current date.

" wrote:

No, I guess I ddn't explain properly.

The attendance tracker has a cell for each date across the top (i.e. 2/16,
2/17, 2/18 etc.). If today is 2/16, I do not want to count the days in the
2/17 or 2/18 column as a day taken, but rather as a day planned.


"CurlyDave" wrote:

On Feb 16, 9:04 am,
icrosoft.com wrote:
Hi I'm using the attendance tracker, with Excel 2003.

When my people turn in their vacation schedules for the year, I enter them
all on the tracker.

I need to calculate the number of vacation days taken vs. the number of days
planned.

How do I do that?

Subtract the two cells?
example:
=B1-A1

What is "the tracker"? Is it a template you are using? A special program you
have? We need more information on it before we can answer your question.

Regards,
Fred.

"
icrosoft.com wrote in
message ...
Hi I'm using the attendance tracker, with Excel 2003.

When my people turn in their vacation schedules for the year, I enter them
all on the tracker.

I need to calculate the number of vacation days taken vs. the number of
days
planned.

How do I do that?

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

The tracker is a Microsoft Template located he
http://office.microsoft.com/en-us/te...33&av=Z XL000


"Fred Smith" wrote:

What is "the tracker"? Is it a template you are using? A special program you
have? We need more information on it before we can answer your question.

Regards,
Fred.

"
icrosoft.com wrote in
message ...
Hi I'm using the attendance tracker, with Excel 2003.

When my people turn in their vacation schedules for the year, I enter them
all on the tracker.

I need to calculate the number of vacation days taken vs. the number of
days
planned.

How do I do that?

While my example is not perfect (having looked at the tracker), you can use
it to come up with formulas that will work. If you put "v" in the cells
where employees will be on vacation, then you may need to use the COUNT
function instead of the SUM for the first formula (because you're counting
the number of "v" entries, not adding ones), but the principle is the same.
Add columns at the end of each Quarterly data sheet and put the formulas in
there. You can then add columns to the summary sheet and sum the numbers
from the four quarterly sheets.

Eric

Thanks, that works.

I'm going to use your example of putting a "1" instead of a "v", that way I
can also track half days by putting in .5.

One last question, if I want to track scheduled days vs. unscheduled days
(i.e. someone called in sick, that's an unscheduled day, I'm filling the cell
with a color, like red, can I query based on the color of the cell?

"egun" wrote:

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

Not that I know of, except through some Visual Basic code in the background.


One last question, if I want to track scheduled days vs. unscheduled days
(i.e. someone called in sick, that's an unscheduled day, I'm filling the cell
with a color, like red, can I query based on the color of the cell?

Okay, thanks for your help.

"egun" wrote:

Not that I know of, except through some Visual Basic code in the background.


One last question, if I want to track scheduled days vs. unscheduled days
(i.e. someone called in sick, that's an unscheduled day, I'm filling the cell
with a color, like red, can I query based on the color of the cell?