I'm trying to create a spreadsheet than can track the amount of sick pay people have left in a rolling year, I'm having difficulty with with getting it to track properly however.
I have two sheets, a Leave sheet, that you enter in the date the person started sickness, and the date the sickness ended.
Column C -Name, Column D - Date from, Column E - Date to, Column F - Total day (=DAYS(E2,D2)), Column G - Work Days (SUM((F2/7)*5))
The second sheet is the employees sheet, that has the following columns
=if(A18<>"",sumifs(Leave!G:G,Leave!D:D,">="&$F$2,Leave!D:D,"<="&$G$2,Leave!D:D,"<=" &now(), Leave!C:C,A18, Leave!B:B,"Yes"),"")
Column A is employee name
F2 has this formula - =DATE(YEAR(G2) - 1, MONTH(G2), DAY(G2)) to work out the date a year ago
G2 is just =Today()
I've taken this from another spreadsheet we used, that tracked the same thing, but for employees with their sickness in hours, so the Leave sheet was filled out differently.
Any help would be appreciated.
EDIT: I have added a link to a copy of the spreadsheet I'm working on.
SUM(F2/7)*5isn't doing what you think it is. The logic is off. If someone were sick on a Friday and the following Monday, your formula would return something like 1.4 days when in actuality it was 2 full days. And
SUMseems unnecessary in any such equation, since you aren't actually adding anything.