Link to home
Start Free TrialLog in
Avatar of bneuman
bneuman

asked on

How do I count number of vacation days taken?

I have a basic attendance spreadsheet. It lists 30 employees time off, both actual and scheduled. I would like to add a column that lists the just actual vacation days taken off since the 1st of the year.

I have a summary sheet that lists all categories...(sick, late, vacation....etc) I'm only interested in actual vacation days taken.

Each month is listed and the for example the vacation category has this formula..
=COUNTIF($L27:$AP27,"v")

It will count all vacation days listed. Some may have pasted and some may be in the future. I need a count of only actual days taken from 01/01/09 thru now(). Not sure how to go about this.
Avatar of lumberjak
lumberjak

Since this is a manual spreadsheet,attendance is taken per day, correct?  Without seeing the spreadsheet, yet by the description you gave, you might be able to use the SumIf function for actual vacation days.  Just like the CountIf function uses "V", the SumIf could use something similar in order to count only the actual days taken.  See the attached spreadsheet as a possible example.  I also had to use the If/Then function :-)
Book1.xls
Avatar of bneuman

ASKER

Unfortunately the spreadsheet isn't setup that way. I've copied 1 page of last years (January) plus Summary to show you layout.
Avatar of bneuman

ASKER

sorry forgot to attach file
attend-08-jan.xls
ASKER CERTIFIED SOLUTION
Avatar of lumberjak
lumberjak

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Also, for the example to work, I had to change the Vacation code from "V" to a "1".
Avatar of bneuman

ASKER

lumberjak,

I believe that will work but I need the "1" to be either a "v" or "V" for my macro to work. Is there a way to make this work with the letter? HR uses this and they only know how to add the letter for the category.

Bill
Hi Bill -
Change the SumIf formula to CountIf (pretty much the same one you used in your initial question).  To be honest, and from what I've seen on your spreadsheets, you could have probably figured this yourself - you have very good Excel skills :-)
Avatar of bneuman

ASKER

lumberjak...

thank you, works well.

Bill