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

<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>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]2[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]Current Date:[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=ffff00]#ffff00[/URL] , align: center"]8/11/2017[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]4[/TD]
[TD="align: center"]Last[/TD]
[TD="align: center"]First[/TD]
[TD="align: center"]Date of Absense[/TD]
[TD="align: center"]Consecutive Date[/TD]
[TD="align: center"]Points[/TD]
[TD="align: center"]Absense or Tardy[/TD]
[TD="align: center"]Days Unexcused[/TD]
[TD="align: center"]Total Points Used[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]5[/TD]
[TD="align: center"]Last[/TD]
[TD="align: center"]First[/TD]
[TD="align: center"]7/2/2017[/TD]

[TD="align: center"]1[/TD]

[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]6[/TD]
[TD="align: center"]7/15/2017[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]7[/TD]
[TD="align: center"]7/22/2017[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]8[/TD]
[TD="align: center"]7/30/2017[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]9[/TD]
[TD="align: center"]8/8/2017[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]10[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=cacaca]#cacaca[/URL] , align: center"]11[/TD]

</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.