#1
|
|||
|
|||
Calculating dates in an employee attendance tracker
Hi all,
I am attempting to make a dynamic employee tracker for attendance. I have attached a sample workbook. The workbook has: Employee Name, Vacation Used, Sick Days Used and Care Days used on the first sheet to summarize the info. The second sheet has the input data. Employee Name, Attendance Type, Start Date and End Date. I used data validation and named ranges to have drop downs for the first two columns. Essentially, I am having issues with finding a formula to calculate the dates for each Employee correlated to the Attendance Type. I have tried a number of combinations using Countifs, sumproduct, Index, If, Index Match, etc. The formula I am attempting to find is dynamic as it is meant to include any new entries. Any help would be greatly appreciated. |
#2
|
||||
|
||||
Hi, which days of the week should be excluded from the calculations? Weekends, holidays?
And from which attendance type?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
I have Saturday and Sunday as weekends to be excluded. I also have a chart (below) for the holidays for exclusion. The sick, vacation, and care days are to have the weekends and holidays excluded.
Date Holiday January 01 2017 New Year's Day February 20 2017 Family Day April 14 2017 Good Friday May 23 2017 Victoria Day July 01 2017 Canada Day August 07 2017 Civic Holiday September 04 2017 Labour Day October 09 2017 Thanksgiving Day November 11 2017 Remembrance Day December 25 2017 Christmas December 26 2017 Boxing Day |
#4
|
|||
|
|||
How about this?
|
#5
|
|||
|
|||
This works perfectly and it is exactly what I was trying to solve.
Thank You! |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Budget Tracker Excel | vill | Excel | 1 | 07-11-2017 01:14 PM |
Calculating Quarterly Dates by Months not Days | Artboy34 | Excel | 3 | 01-28-2016 09:47 AM |
office template: employee attendance tracker | LeylandRick | Excel | 2 | 01-08-2016 03:35 AM |
Turn Around Time tracker | call2shree | Excel | 2 | 08-05-2015 10:51 AM |
Calculating dates | Daria11 | Word VBA | 1 | 06-08-2011 06:54 PM |