Count number of consecutive days by date

[malek_ali7sas]

dear all

please i need your help to know the formula for the attached file

the file mention the worksheet for the employees

if the employee absent from 1 to 3 consecutive days its mean that absent 1 time

please i want to know how it make by formula because i have more than 500 employees

please help me

[hemesh]

hi and welcome to the forum
copy paste below in G2 then hold control and shift together then hit enter to make it array formula2

Please Login or Register  to view this content.

[malek_ali7sas]

thank you sir for your help

please sir can you help in the attached file

i have 3 rules in it

1 rule, how many time he absent from 1 to 3 consecutive days

2 rule, how many time he absent from 4 to 6 consecutive days

3 rule, how many time he absent from 7 to 10 consecutive days

[silambarasan.J]

Hi,

please see through attachment.

[malek_ali7sas]

thank you sir

its not what i mean

what i mean its consecutive days in the same
for Ex:
some of employee absent 3 days consecutive its mean deduct 1 day then he attend 4 days then absent 1 day the deduct should be 2 days because he Repeated absence(rules 1)

[hemesh]

I am signing off due to some wok and Once I am free I will have a look at it,

May be in meantime someone will pick up this topic

[silambarasan.J]

thanks for your clarification.i need some more details.please give your requirement for the following emp.id.

what is the expert the result.

Emp No. rules A from 1 to 3 days rules B from 4 to 6 days rules C from 7 to 10 days
20957
25798
20809
20824
25122
20840

[hemesh]

Copy paste below in B2 then hold control and shift together and then hit enter to make it array formula

Please Login or Register  to view this content.

Drag down

Change red part to {4,5,6} in C2 then In D2 change red part to {7,8,9,10}

drag down

check whether it is giving you correct results or not

[malek_ali7sas]

check the file please

this is the rule 1 from 1-3 consecutive day

thank you for your time

[malek_ali7sas]

thank hemesh for your time


if expert the result for 50403 i think it should be like this, rule (A) the result 1 his absent consecutive 5&6 Feb , rule (C) it should be 1, his absent consecutive (8,9,10,11,12,13,14,15)Feb

now in your function it become only on rule (C) =1

[hemesh]

I tried over this a lot may be I am missing something, I am forwarding this to seniors members of forum, who have more in depth knowledge,hope they will give proper solution to this.

[newdoverman]

Maybe this will help you.
I used a helper column on the Master worksheet to count consecutive days following the rules. I an apparent run of consecutive days is interrupted by missing a day, the count starts again. With this amount of data I can't check everything but what I did check, was correct.
Formula for helper column (G) on Master worksheet. Enter in G2 and fill down. Column G must have a Title or it won't work. This is the most complicated part of the whole problem.

Please Login or Register  to view this content.

On Sheet2!B2 enter this formula and fill down.

Please Login or Register  to view this content.

On Sheet2!C2 enter this formula and fill down.

Please Login or Register  to view this content.

On Sheet2!D2 enter this formula and fill down.

Please Login or Register  to view this content.

I also created a small summary table totalling the absences for each rule. It should be self explanatory.