Greater than 5 days formula
 

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

Greater than 5 days formula
 

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

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.

"Rachael F" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

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" wrote:

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

=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" wrote:

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" wrote:

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

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" wrote:

=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" wrote:

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" wrote:

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

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),C1A1),if(A1C1, "Failed","Passed"),if(A1="",if(networKdays(B1,C1,h oliday_range)5,"Failed","Passed"),if(networKdays( B1,A1,holiday_range)5,"Failed","Passed")))



"Rachael F" wrote:

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" wrote:

=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" wrote:

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" wrote:

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

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" wrote:

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),C1A1),if(A1C1, "Failed","Passed"),if(A1="",if(networKdays(B1,C1,h oliday_range)5,"Failed","Passed"),if(networKdays( B1,A1,holiday_range)5,"Failed","Passed")))



"Rachael F" wrote:

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" wrote:

=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" wrote:

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" wrote:

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

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


"Rachael F" wrote:

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" wrote:

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),C1A1),if(A1C1, "Failed","Passed"),if(A1="",if(networKdays(B1,C1,h oliday_range)5,"Failed","Passed"),if(networKdays( B1,A1,holiday_range)5,"Failed","Passed")))



"Rachael F" wrote:

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" wrote:

=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" wrote:

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" wrote:

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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

Greater than 5 days formula
 

Brilliant. What a star!

Thank you so much.

"bj" wrote:

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


"Rachael F" wrote:

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" wrote:

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),C1A1),if(A1C1, "Failed","Passed"),if(A1="",if(networKdays(B1,C1,h oliday_range)5,"Failed","Passed"),if(networKdays( B1,A1,holiday_range)5,"Failed","Passed")))



"Rachael F" wrote:

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" wrote:

=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" wrote:

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" wrote:

=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" wrote:

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

"bj" wrote:

=if(A1B1+5,"Failed","Passed")

"Rachael F" wrote:

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