Solved

How do I count number of vacation days taken?

Posted on 2009-05-13
8
364 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
Comment
Question by:bneuman
  • 4
  • 4
8 Comments
 
LVL 4

Expert Comment

by:lumberjak
Comment Utility
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

by:bneuman
Comment Utility
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

by:bneuman
Comment Utility
sorry forgot to attach file
attend-08-jan.xls
0
 
LVL 4

Accepted Solution

by:
lumberjak earned 350 total points
Comment Utility
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 4

Expert Comment

by:lumberjak
Comment Utility
Also, for the example to work, I had to change the Vacation code from "V" to a "1".
0
 

Author Comment

by:bneuman
Comment Utility
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

by:lumberjak
Comment Utility
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

by:bneuman
Comment Utility
lumberjak...

thank you, works well.

Bill
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now