Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 09-29-2017, 09:15 AM
Knox05 Knox05 is offline Windows 7 64bit Office 2013
Novice
 
Join Date: Sep 2017
Posts: 3
Knox05 is on a distinguished road
Default 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.
Attached Files
File Type: xlsx Tracker.xlsx (9.3 KB, 10 views)
Reply With Quote
  #2  
Old 10-04-2017, 08:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is online now Windows 7 64bit Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 1,940
Pecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nicePecoflyer is just really nice
Default

Hi, which days of the week should be excluded from the calculations? Weekends, holidays?
And from which attendance type?
Reply With Quote
  #3  
Old 10-04-2017, 09:13 AM
Knox05 Knox05 is offline Windows 10 Office 2016
Novice
 
Join Date: Sep 2017
Posts: 3
Knox05 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 10-04-2017, 11:10 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 88
ArviLaanemets is on a distinguished road
Default

How about this?
Attached Files
File Type: xlsx Tracker.xlsx (15.4 KB, 9 views)
Reply With Quote
  #5  
Old 10-05-2017, 02:50 PM
Knox05 Knox05 is offline Windows 10 Office 2016
Novice
 
Join Date: Sep 2017
Posts: 3
Knox05 is on a distinguished road
Default

Quote:
Originally Posted by ArviLaanemets View Post
How about this?
This works perfectly and it is exactly what I was trying to solve.

Thank You!
Reply With Quote
Reply

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 10:50 AM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft