Solved

# How do I count number of vacation days taken?

Posted on 2009-05-13
Medium Priority
374 Views
Last Modified: 2012-06-27
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.
0
Question by:bneuman
• 4
• 4
8 Comments

LVL 4

Expert Comment

ID: 24374313
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
0

Author Comment

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

Author Comment

ID: 24374506
sorry forgot to attach file
attend-08-jan.xls
0

LVL 4

Accepted Solution

lumberjak earned 1400 total points
ID: 24375542
Try this - I added a Vacation Used column in the January tab and used a SumIf formula to add the vacation days taken (I added some for this example).  Then, I used a vlookup formula on the summary tab for the last name and linked it to the Vacation Used column I added in the month.

See attached example
Copy-of-attend-08-jan.xls
0

LVL 4

Expert Comment

ID: 24375551
Also, for the example to work, I had to change the Vacation code from "V" to a "1".
0

Author Comment

ID: 24376163
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
0

LVL 4

Expert Comment

ID: 24376753
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 :-)
0

Author Closing Comment

ID: 31580936
lumberjak...

thank you, works well.

Bill
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month16 days, 19 hours left to enroll

#### 862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.