Sumproduct formula based on date

[Binaxxx]

Hi Everyone,

After searching for a long time I have decided to post my issue :

I have a absence daily calendar where the days off are recorded for every employees .

Given that this is a 12 months rolling system I must take into account both current and previous years records.

The absence days are recorded but we trigger warning based on the total of time an employee will be absent.

For example an employee can have a total of 10 days of absences since the beginning of this year but - 3 consecutives days in May + 7 consecutives days in June . This will means that person has been off 2 times . If there is than 3 times absence within the last 3 months we trigger a warning .

Please find attached my example :

I have been able to find a sumproduct formula to count the number of times .

However I am unable to change or move the arrays based on a day .

Please help

 

[Luke M]

Hi Binaxxx, and welcome to the forum!

I don't fully understand your comment about hte -3 days + 7 in June. Could you elaborate on your example and show us what result the formula should be coming up with? I see you have formulas in A2:A4, but I have no idea currently what they are attempting to calculate.

 

[Xiq]

Hi Binaxxx,

Not sure if this is what you are looking for, but have a look at this:

=SUMPRODUCT(--(Date_range>Date_90days_ago),--(Date_range<=Today),--(C2:NB2<>D2:NC2),--(C2:NB2<>"1"),--(D2:NC2<>""))

 

[Binaxxx]

Hi Binaxxx,

Not sure if this is what you are looking for, but have a look at this:

=SUMPRODUCT(--(Date_range>Date_90days_ago),--(Date_range<=Today),--(C2:NB2<>D2:NC2),--(C2:NB2<>"1"),--(D2:NC2<>""))

Hi Xip,

This is working perfectly, many thanks for your help