Greater than 5 days formula

[Rachael F]

I would like help with a formula for the following:

If A1 is more than 5 working days greater than B1 = 'Failed'
If A1 is equal to or less than 5 working days greater than B1 = 'Passed'

Many thanks.

Rachael

 

[bj]

=if(A1>B1+5,"Failed","Passed")

 

[Rachael F]

Thanks bj. What if I only want to count working days?

 

[William Horton]

Use the WORKDAY function in your If statement. It is in the Analysis
Toolpack addin if it isn't in your Excel already.

Hope that helps.

 

[bj]

=if(networKdays(B1,A1,holiday_range_if _wanted)>5,"Failed","Passed")
you have to have the analysis toolpac added for this to work.
the holiday range is optional. Check help for more info.

 

[Rachael F]

Thanks bj.

Leading on from my original question, how can the formula
'=if(networKdays(B1,A1,holiday_range_if _wanted)>5,"Failed","Passed")' be
changed to incorporate the following:

if A1 is blank, base the calculation on C1 instead

 

[bj]

=if(A1="",if(networKdays(B1,C1,holiday_range_if
_wanted)>5,"Failed","Passed"),if(networKdays(B1,A1,holiday_range_if
_wanted)>5,"Failed","Passed"))

would be one option

 

[Rachael F]

Spot on, bj. Thanks very much.

Whilst I'm on a roll....how can I change the formula to also include the
following:

If there is a date in C1, compare it with A1.
If A1 & C1 are equal = 'Passed'
If A1 is greater than C1 = 'Failed'

Many thanks for your help.

Rachael

 

[bj]

The priority of the comparisons becomes important
Assuming the earlier equation is only needed if either C1 or A1 is not
entered of if C1 is greater than A1
This assumes that at least one of A1, C1 is a date

=if(and(isnumber(C1),isnumber(A1),C1>A1),if(A1>C1,"Failed","Passed"),if(A1="",if(networKdays(B1,C1,holiday_range)>5,"Failed","Passed"),if(networKdays(B1,A1,holiday_range)>5,"Failed","Passed")))

 

[Rachael F]

Hi

If there is a date in C1 then it should only be compared to A1 (There will
always be a date in A1).
If A1 is greater than C1, it should 'Fail'. If A1 is equal to C1, it should
'Pass' (There will never be an earlier date).

If there is no date (blank) in C1, then B1 should be compared to A1 (There
will always be a date in A1 & B1).
If A1 is more than 5 working days greater than B1, it should 'Fail'. If it
is 5 days or less, it should 'Pass'.

Hope that helps.

Many thanks.

Rachael

 

[bj]

=if(C1<>"",if(C1=A1,"Pass","Fail"),if(networKdays(B1,A1,holiday_range_if_wanted)>5,"Failed","Passed"))

 

[Rachael F]

Brilliant. What a star!

Thank you so much.

=if(C1<>"",if(C1=A1,"Pass","Fail"),if(networKdays(B1,A1,holiday_range_if_wanted)>5,"Failed","Passed"))