?
Solved

How do I count number of vacation days taken?

Posted on 2009-05-13
8
Medium Priority
?
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 4

Expert Comment

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

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

by:bneuman
ID: 24374506
sorry forgot to attach file
attend-08-jan.xls
0
Office 365 Training for IT Pros

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 4

Accepted Solution

by:
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

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

Author Comment

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

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

by:bneuman
ID: 31580936
lumberjak...

thank you, works well.

Bill
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

765 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