Calculating number of occurrences in the last 12 months (weekdays only)

[VB17]

Hi all

I am very new to Forums and require some help please. I have an absence tracker that I need to calculate the number of occurrences an employee has been sick in the last 12 months.

I have a column that has dates in including weekend and next to that is columns of employee names.

If an employee is is off, then I would complete "Sick" on the correct employee and the correct corresponding date.

I need a formula that calculates the number occasions in the last 12 months and employee has been sick (not days...just occasions) - It's been driving me crazy!

For example, if an employee was sick in March 2020 (2 days), May 2020 (5 days) and September 2020 (3 days). This would be 10 days sick in 12 months (3 occasions in 12 months).

I need the formula to group continuous days if 1 period of absence runs into another week but to ignore the weekends within that period. I have attached a sample version of it.

Any help you can give me will be appreciated!!!

Thank you

[XLent]

there will be a few options here, one:

=1+SUMPRODUCT(SIGN(NETWORKDAYS(AGGREGATE(15,6,$A$2:$A$735/(B$2:B$735="Sick"),SEQUENCE($H2-1,,1)),AGGREGATE(15,6,$A$2:$A$735/(B$2:B$735="Sick"),SEQUENCE($H2-1,,2)))-2))
copied to right

would return 3, and 4 for EE 1 & 2 respectively

[XLent]

@VB17 -- ignore the above, couple of typos (e.g. $H2 rather than H$2), apologies - and would have ignored the last 12 month requirement:

H3: =1+SUMPRODUCT(SIGN(NETWORKDAYS(AGGREGATE(14,6,$A$2:$A$735/(C$2:C$735="Sick"),SEQUENCE(H$2-1,,H$2,-1)),AGGREGATE(14,6,$A$2:$A$735/(C$2:C$735="Sick"),SEQUENCE(H$2-1,,H$2-1,-1)))-2))
copied to I3

the above would return 3, and 5 ... rather than 3, and 4 -- you might need to explain why you think there are 4 discrete sequences rather than 5, for Ee 2.
(11-19 May, 15-19 Jun, 29-Jun to 10-Jul, 14-18 Sep and 3-13 Nov.)

[VB17]

Hi XLent

Thank you for taking the time to do this. I am getting an error message in the cell using your new formula.

EE2 should be 5 occurrences (sorry, typo on my part)

[kvsrinivasamurthy]

In H3 copied to H4

Please Login or Register  to view this content.

To enter ARRAY formula
Copy and paste the formula in cell
Press F2
Press Ctrl+Shift+Enter together
Excel covers the formula with {}.

[XLent]

Hi XLent; Thank you for taking the time to do this. I am getting an error message in the cell using your new formula.

Yet another typo on my part, I'm afraid!

H3: =1+SUMPRODUCT(SIGN(NETWORKDAYS(AGGREGATE(14,6,$A$2:$A$735/(B$2:B$735="Sick"),SEQUENCE(H$2-1,,H$2,-1)),AGGREGATE(14,6,$A$2:$A$735/(B$2:B$735="Sick"),SEQUENCE(H$2-1,,H$2-1,-1)))-2))
copied to right

[VB17]

Thank you XLent and kvsrinivasamurthy - this has worked a treat. Much appreciated

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.