Test Cell Values Before Adding Weekdays

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


    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


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


    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:


    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:


    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.


  • 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?


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




    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!