Calculate difference between dates only if a condition is met

[hayleyawright]

Hello, i'm trying to figure out how to calculate the number of days between dates where a certain condition is met (example of what i'm trying to figure out attached).

In column D, I want to calculate the difference between only the dates when attendance is yes. e.g. I would want B4 - B2 to inform my frequency result.

Thanks very much!

[AliGW]

Welcome to the forum.

What results are you expecting in the frequency column? Manually calculate these for your sample data and add them too the sample workbook, please.

[AliGW]

Just a guess, really - is this what you want?

=MAXIFS($B$2:$B$5,$A$2:$A$5,A2,$C$2:$C$5,"Yes*")-MINIFS($B$2:$B$5,$A$2:$A$5,A2,$C$2:$C$5,"Yes*")

I had to add the * because you have some trailing spaces in your Yes/No column. Ideally get rid of the trailing spaces and then you can take the * out of the formula.

[kvsrinivasamurthy]

Try this
In D2 then copy down.

=IF(C2="Yes",IFERROR($B2-LOOKUP(2,1/($C$1:$C1="Yes"),$B$1:$B1),""),"")

[hayleyawright]

Hi Ali, thanks so much for your help! I have just updated the example spreadsheet (and have removed the trailing space) with the answer I would be looking for. I have added an extra step of just calculating difference between work days (mon - fri) too.

[hayleyawright]

Hello, thanks so much you're amazing! How would you go about adding in just NETWORKDAYS into that formula?

[AliGW]

Which of us are you asking?

Try this:

=IF(C2="Yes",IFERROR(ABS(NETWORKDAYS.INTL($B2,LOOKUP(2,1/($C$1:$C1="Yes"),$B$1:$B1))),""),"")

[bebo021999]

Base on your NETWORKDAYS, just replace B2 with:
LOOKUP(2,1/($A$1:A2=A3)/($C$1:C2="Yes"),$B$1:B2)
to define the most recent date with "Yes" and same Staff member.

From D3 (D2 =0):

Please Login or Register  to view this content.

[hayleyawright]

Thankyou very much - very helpful

Which of us are you asking?

Try this:

=IF(C2="Yes",IFERROR(ABS(NETWORKDAYS.INTL($B2,LOOKUP(2,1/($C$1:$C1="Yes"),$B$1:$B1))),""),"")

[hayleyawright]

Thank-you!!

Base on your NETWORKDAYS, just replace B2 with:
LOOKUP(2,1/($A$1:A2=A3)/($C$1:C2="Yes"),$B$1:B2)
to define the most recent date with "Yes" and same Staff member.

From D3 (D2 =0):

Please Login or Register  to view this content.

[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.