 # Test Cell Values Before Adding Weekdays

=IF(A2="","",IF(C2>TODAY(),"",IF(E2="",IF(B2="1st",WORKDAY(A2,4,\$H\$1),WORKDAY(A2,5,\$H\$1)),C2)))

What I am trying to do is cause (where the formula is) to:

If A2 is empty, put nothing,
If C2 is greater than today put nothing, otherwise put C2
If E2 is empty then if b2 is "1st" add 4 workdays to A2, otherwise add 5 workdays.

I seem to have a problem with getting the formula to put the value of C2 if it is not greater than today In otherwords if the date in C2 is 6th Jan, i want 6th Jan as the result of the formula.

Hope this makes sense

• Re: Nested If Statement

Mar0507,

I have thrown your formula into a blank spreadsheet and it works fine - have you loaded the Analysis ToolPak add in - the formula Workday need it to run...

GB

But I always say luck is where preparation meets opportunity (Justin Langer 23/11/01)

• Re: Nested If Statement

Interesting. It is not the WORKDAY that does not seem to work, it is the C2 part that seems to fail. Maybe I didn't explain properly.

If C2 is greater than today leave blank, otherwise put C2, however when there is a date in E2 then the result of the formula would be blank.

Thanks[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I seem to have solved the puzzle, the formula should be:

=IF(A3="","",IF(C3>TODAY(),"",IF(AND(C3<=TODAY(),E3=""),C3,IF(E3="",IF(B3="1st",WORKDAY(A3,4,\$G\$1),WORKDAY(A3,5,\$G\$1)),""))))

Thanks for your help, and I hope this helps someone in the future

• Re: Nested If Statement

As written here your formula can't ever return a result from either of the WORKDAY formulas. The second E3="" can never be TRUE because you have already exhausted all the circumstances in which it could be, should that part be E3<>""?

• Re: Nested If Statement

• Re: Test Cell Values Before Adding Weekdays

The formula I wrote does seem to work for my purposes. I have tested all scenarios.

However When I adjust the formula to:

=IF(A3="","",IF(C3>TODAY(),"",IF(AND(C3<=TODAY(),E3=""),WORKDAY(C3,2,\$G\$1),IF(E3="",IF(B3="1st",WORKDAY(A3,4,\$G\$1),WORKDAY(A3,5,\$G\$1)),""))))

It seems on the face of it to work, but a formula that looks at a column where this formula resides (Column D) then fails to work. The formula looking at Column D is:

SUMIF(D2:D50,I2,E2:E50) - Column D is a Date, as is Column I (for which it is comparing) and Column E is an amount. it gives a value of 0

The only difference as far as I can see is I am asking the complex formula to ADD 2 days to the date in C if the value of C is less than or equal to AND if E is empty.

Thanks

• Re: Test Cell Values Before Adding Weekdays

Quote from mar0507

The formula I wrote does seem to work for my purposes. I have tested all scenarios.

I see you've changed the formula slightly but I still think you have a problem. You have 3 workday functions in the formula but, as written, there can never be any circumstances where the last 2 will be applied.

Can you give an example of values contained in A3, B3, C3 and E3 that would lead to either the 4 or 5 day workday functions being actioned? Are you sure the formula gives the result required in those circumstances

Not sure why the SUMIF wouldn't work.

Stupid Question: Are you sure you have dates in D2:D50 that match I2?

Is the date in I2 generated by a formula or just input? Make sure it doesn't contain a time as well as a date otherwise it won't match with dates in D2:D50.

If that isn't the problem then the only other possibility I can think of is that column E is text formatted. Does this formula work?

=SUMPRODUCT((D2:D50=I2)*E2:E50)

• Re: Test Cell Values Before Adding Weekdays

mar0507, please acknowledge my post above and that you have read what has been requested.

• Re: Test Cell Values Before Adding Weekdays

I have now overcome my SUMIF problem, in that I have split the complex formula in two. They now read:

=IF(ISBLANK(A2),"",IF(C2<>"",E2,IF(ISBLANK(G2),IF(B2="1st",WORKDAY(A2,4,),WORKDAY(A2,5,)),"")))

and

=IF(OR(C2="",G2<>""),"",IF(C2>TODAY(),"",IF(C2<=TODAY(),WORKDAY(C2,2,\$H\$1))))

The values in each column are:

Col A: Date
Col B: 1st or 2nd
Col C: Date (or Empty)
Col D: First Formula
Col E: Second Formula
Col F: Amount (In Pounds)
Col G: Date

All seems to work perfectly, I have tested ALL scenarios and the result is as I would have expected.

Thanks again for you help.

• Re: Test Cell Values Before Adding Weekdays

Thanks for your email confirmation and sharing your solution. You have redeemed yourself ## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!