Absence Tracker

[VSeaver]

I have created an absence tracker that tracks points accured on a rolling 12 month system. If an employee takes an unexcused absence he gets a point. After 12 months, a point will be taken back. I believe I have completed this with this formula in I5: =SUMIFS($F$5:$F$11,$D$5:$D$11,"<="&DATE(YEAR(C2),MONTH(C2),DAY(C2)),$D$5:$D$11,">="&DATE(YEAR(C2)-1,MONTH(C2),DAY(C2)))

My next step is what I am asking. I want to add to this if an employee takes an unexcused day and doesn't take another one for 6 months, then another point will be taken back as well. I may be over thinking this and maybe there is an easier way. I attached an HTML of what I have. This is the first time I tried Excel Jeanie so forgive me if doesn't work.

Main

	B	C	D	E	F	G	H	I
2	Current Date:	8/11/2017
3
4	Last	First	Date of Absense	Consecutive Date	Points	Absense or Tardy	Days Unexcused	Total Points Used
5	Last	First	7/2/2017		1			5
6	7/15/2017		1
7	7/22/2017		1
8	7/30/2017		1
9	8/8/2017		1
10
11

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:112px;"><col style="width:108px;"><col style="width:145px;"><col style="width:145px;"><col style="width:58px;"><col style="width:145px;"><col style="width:145px;"><col style="width:128px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula C2 =TODAY() I5 =SUMIFS($F$5:$F$11,$D$5:$D$11,"<="&DATE(YEAR(C2),MONTH(C2),DAY(C2)),$D$5:$D$11,">="&DATE(YEAR(C2)-1,MONTH(C2),DAY(C2))) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4



Thanks.