Solved

How do I count number of vacation days taken?

Posted on 2009-05-13
8
369 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 4

Accepted Solution

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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