Formula for counting occurrences by individual within a rolling year period

[naughtsncross]

Good Afternoon!

I am hoping that someone out there may be able to help me with a hurdle I have reached.

In a (large) nutshell, I am trying to design an absence spreadsheet to be used to calculate whether someone is entitled to receive sick pay. I have the spreadsheet calculating a person's annual entitlement based on their length of service, the dates of the new absence and the number of days to be paid this time. What I now need to do is to add a cell which looks at a list of previous absences and then shows how many paid days the person has received in the 365 days prior to the start date of the current absence.

I have got as far as a formula which pulls back the total number of days paid using the employee number, I am just struggling with limiting it to absences within the last 365 days.

If you are still with me, I will attach a sample of what I am working on (when I can work out how to do this!)

Hope you can help!

 

[naughtsncross]

Re: Need assistance with a formula for counting occurrences by individual within a rolling year period

I can't seem to attach a file so will summarise here:

My main tab is set up as this, this is entered each month.

A

B

C

D

E

F

G

H

I

J

K

L

1

Employee Number

Employee Name

Region

Start Date

Entitlement Days

First Date of Absence

Last Date of Absence

Total Days Absent

Unpaid Days

Payable Days

Days already paid in previous 12 months

Entitlement Days Remaining

2

1000001

Sample 1

1

01/08/2017

9.86

09/10/2017

14/10/2017

6

3

3

*

3

<tbody>
</tbody>

* This is where I need to amend the formula to restrict the response to the last 365 days.
Cell K2 currently reads: =SUMIFS('Sheet2'!K:K, 'Sheet2'!A:A, 'Sheet1'!A2)

 

[naughtsncross]

Re: Need assistance with a formula for counting occurrences by individual within a rolling year period

Can anyone help me?

 

[naughtsncross]

Re: Need assistance with a formula for counting occurrences by individual within a rolling year period

I've managed to do it! It's taken me 3 days but it's now working.
I added in a column after payable days to calculate the date 12 months prior to the absence start date, then swapped the IFS statements around to look at the dates first, then pull back the number of payable days after that date that matched the employee number.

=SUMIFS('Sheet2'!K:K, 'Sheet2'!G:G,">"&Sheet1!L2, 'Sheet2'!A:A, 'Sheet1'!A2)