Absence Tracker

[CI Dan]

Hi all,
I'm trying to develop a absence tracker; I managed to cover most of the requirement apart from one that I need your help with.
Workbook contains dates in row 12 and each individual absence is recorded in rows below as 1, 0.5 (day) etc. We are measuring absence on rolling 12 months basic, the formula I used to calculate total number of absence days is: =SUMIFS(I13:AET13,$I$12:$AET$12,">="&$E$11,I13:AET13,"<="&NOW()) where E11 contain start date (today - 12 months). My issue sits with calculating number of occurrences. So for example: If an employee misses a week, comes back to work for a week and then misses another two days; how do I return value of 2 (as of two occurrences)?

Thanks in advance
Dan

 

[wideboydixon]

I *think* it's this but I haven't tested it:

=SUM(IF(FREQUENCY(IF($I$12:$AET$12>=$E$11,IF($I$12:$AET$12<=NOW(),IF($I$13:$AET$13<>"",COLUMN($I$13:$AET$13)))),IF($I$12:$AET$12>=$E$11,IF($I$12:$AET$12<=NOW(),IF($I$13:$AET$13="",COLUMN($I$13:$AET$13)))))>=1,1))

Entered as an array formula with Ctrl+Shift+Enter

WBD

 

[CI Dan]

Works perfect; thank you very much.