0

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.

https://docs.google.com/spreadsheets/d/1_D6GQtzzw5TbyNfmwuKPZcyTsVHB8TbM/edit?usp=sharing&ouid=101909992625126971009&rtpof=true&sd=true

Improve this question
2
  • Keep in mind that we can't see your spreadsheet. We don't know your data or end goal. You're asking the volunteer contributors here to imagine an awful lot, and then to hold all that in mind while then mentally writing formulas (because we don't have a workspace) around that. I encourage you to share a link to your spreadsheet (or to a copy of it, or to a sanitized and limited copy containing enough realistic data to convey the problem and the goal as well the your hand-entered results you'd like to see formulas produce).
    – Erik Tyler
    Commented Apr 8, 2022 at 11:56
  • I will tell you at a glance that SUM(F2/7)*5 isn'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 SUM seems unnecessary in any such equation, since you aren't actually adding anything.
    – Erik Tyler
    Commented Apr 8, 2022 at 11:58

1 Answer 1

Reset to default
0

Use the following formula:

=sumifs(F:F,C:C,"Employee Name",D:D,"<"&today()-365,E:E,"<="&today())

, substituting Employee Name and dates for the dates you need.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.