Test Cell Values Before Adding Weekdays

    Hi all, can someone please help, I have the followinf formula


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


    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.

Participate now!

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

Register Yourself Login