Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-29-2017, 09:15 AM
Knox05 Knox05 is offline Calculating dates in an employee attendance tracker Windows 7 64bit Calculating dates in an employee attendance tracker Office 2013
Novice
Calculating dates in an employee attendance tracker
 
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, 18 views)
Reply With Quote
  #2  
Old 10-04-2017, 08:58 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Calculating dates in an employee attendance tracker Windows 7 64bit Calculating dates in an employee attendance tracker Office 2010 64bit
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

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
Reply With Quote
  #3  
Old 10-04-2017, 09:13 AM
Knox05 Knox05 is offline Calculating dates in an employee attendance tracker Windows 10 Calculating dates in an employee attendance tracker Office 2016
Novice
Calculating dates in an employee attendance tracker
 
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 Calculating dates in an employee attendance tracker Windows 8 Calculating dates in an employee attendance tracker Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

How about this?
Attached Files
File Type: xlsx Tracker.xlsx (15.4 KB, 23 views)
Reply With Quote
  #5  
Old 10-05-2017, 02:50 PM
Knox05 Knox05 is offline Calculating dates in an employee attendance tracker Windows 10 Calculating dates in an employee attendance tracker Office 2016
Novice
Calculating dates in an employee attendance tracker
 
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 dates in an employee attendance tracker Calculating Quarterly Dates by Months not Days Artboy34 Excel 3 01-28-2016 09:47 AM
Calculating dates in an employee attendance tracker 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 in an employee attendance tracker Calculating dates Daria11 Word VBA 1 06-08-2011 06:54 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:27 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft