0

I have a file showing how many paid sick days an employee has remaining, we get five days each on a rolling 365 day basis. So if someone is sick 24/05/16, 25/05/16, 26/05/16, 27/05/16, 28/05/16, they'll get paid for those 5 days. They will not be paid if they are sick on any day between 28/05/16 and 23/05/17. And if they are not sick from 24-28/05/16, they are re-entitled to their 5 days again.

We have a calendar of 365 days and we fill it in when someone is sick, then check how many days they have been sick in the past 365 days and fill in the table each time manually.

Basically, is there a way of automating the counting how many sick days paid someone gets within a 365 day timeframe?

1
  • Welcome to Super User! It would be helpful if you posted a sample of what your data looks like (how it is arranged). Please edit your question to include this.
    – Excellll
    Jul 10, 2016 at 16:16

1 Answer 1

1

You can do it even without using macros, this formula checks for you whether somebody had 5+ paid sick days in the last year:

=IF(B3<>"",IF(COUNTIFS($A$2:$A2,">="&$A3-365,C$2:C2,"=yes")>=5,"no","yes"),"")

(note: I've just check last 365 days instead of one calendar year, you may want to use different calculation).

enter image description here

4
  • Thank you very much for your swift answer :D Is there a way to count how many there are left from 365 days ago up to today as a number from 0-5? Jun 28, 2016 at 14:01
  • yes, COUNTIFS($A$2:$A2,">="&$A3-365,C$2:C2,"=yes") returns number of paid sickdays, so 5-COUNTIFS(...) return what's left Jun 28, 2016 at 14:06
  • Cool thanks man, did that then used VLOOKUP to view the current amount left to be put into a single cell Jun 28, 2016 at 14:29
  • I'm happy it helped you. please don't forget to accept this as an answer to mark your question solved. Jun 28, 2016 at 14:34

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .