Employee Vacation & Sick Tracker

[kowen]

I need help with my current employee and sick and vacation tracker. Vacation starts over on the anniversary of hire date. I have one spreadsheet set up to track days they are absent. I have a SUMIFS formula to return that info to the summary page to calculate how much time taken and how much time remains. My problem is I need to sum only the amount taken within their vacation period so that this automatically starts over every year without me having to go in and delete old dates.

For example: Kevin's hire date is 4/11/2005. His current vacation period is 4/11/2017-4/10/2018 and he has used 8 hours of vacation. On 4/11/2018 his vacation will start over and he will be given 160 hours (no rollovers from last period.)

What formula can be used that will sum only the dates of his current vacation period without having to delete the ongoing record of absences?

 

[CalcSux78]

Welcome to the forum...

Should be relatively easy to do with worksheet formulas. All depends on your layout.

Here's a rough example where this entire range is formatted as a table(named Log):

Excel 2010
	B	C	D	E	F	G
2	Employee	Hire Date	Start	Stop	Vacation Allotted	Time Used
3	Jaromir Jagr	10/9/2004	10/9/2016	10/8/2017	160	0
4	Grant Fuhr	1/5/2008	1/5/2017	1/4/2018	160	0
5	Duncan Keith	6/13/2015	6/13/2017	6/12/2018	160	0
6	Tim Horton	3/31/2004	3/31/2017	3/30/2018	160	8
7	Mario Lemieux	2/16/2015	2/16/2017	2/15/2018	160	16
Sheet1

<table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF " ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF ;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5 ;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">D3</th><td style="text-align:left">=IF(<font color="#0000FF">TODAY(<font color="#FF0000"></font>)>=DATE(<font color="#FF0000">YEAR(<font color="#00FF00">TODAY(<font color="#800080"></font>)</font>),MONTH(<font color="#00FF00">C3</font>),DAY(<font color="#00FF00">C3</font>)</font>),DATE(<font color="#FF0000">YEAR(<font color="#00FF00">TODAY(<font color="#800080"></font>)</font>),MONTH(<font color="#00FF00">C3</font>),DAY(<font color="#00FF00">C3</font>)</font>),DATE(<font color="#FF0000">YEAR(<font color="#00FF00">TODAY(<font color="#800080"></font>)</font>)-1,MONTH(<font color="#00FF00">C3</font>),DAY(<font color="#00FF00">C3</font>)</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">E3</th><td style="text-align:left">=DATE(<font color="#0000FF">YEAR(<font color="#FF0000">D3</font>)+1,MONTH(<font color="#FF0000">D3</font>),DAY(<font color="#FF0000">D3</font>)-1</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5 ;color: #161120">G3</th><td style="text-align:left">=SUMIFS(<font color="#0000FF">Vacation[Time],Vacation[Employee],[@Employee],Vacation[Date],">="&[Start],Vacation[Date],"<="&[Stop]</font>)</td></tr></tbody></table></td></tr></table><br/>

I then setup a second table (named Vacation) as such:

Excel 2010
	B	C	D
11	Employee	Date	Time
12	Tim Horton	1/23/2017	8
13	Tim Horton	5/1/2017	8
14	Mario Lemieux	2/28/2017	16
Sheet1

Note: The first entry is outside the vacation period so it is not added into the time used calculation.

 

[CalcSux78]

Formula for column D:
=IF(TODAY()>=DATE(YEAR(TODAY()),MONTH(C3),DAY(C3)),DATE(YEAR(TODAY()),MONTH(C3),DAY(C3)),DATE(YEAR(TODAY())-1,MONTH(C3),DAY(C3)))

(It appears my post cut off part of the formula)