IF, AND, ISBLANK formula

Options

Here is the scenario:

If Cell A is BLANK, the value of Cell B is TEXT, what is the NETDAYS between Cell C and Cell D?

=IF(AND(ISBLANK([CellA@row), ([CellB@row = "All Comments Resolved")), NETDAYS([CellC]@row, [CellD]@row))


Please help!

Tags:

Best Answers

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @SJTA,

    If you don't mind what the text value in CellB is:

    =IF(AND(CellA@row = "", ISTEXT(CellB@row)), NETDAYS(CellC@row, CellD@row))

    If you want CellB to be a particular value:

    =IF(AND(CellA@row = "", CellB@row = "Your text here"), NETDAYS(CellC@row, CellD@row))

    Either of these should work - if you're getting an error, what is it?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Your formula will have been given errors due to some missing square brackets on the column references. This should work if you wanted ISBLANK instead:

    =IF(AND(ISBLANK([CellA]@row), [CellB]@row = "All Comments Resolved"), NETDAYS([CellC]@row, [CellD]@row))

    The missing brackets were on the ends of CellA & CellB, which stopped your formula from functioning properly.

Answers

  • ker9
    ker9 ✭✭✭✭✭✭
    Options

    Hi @SJTA,

    This is very similar to another question you posted. Perhaps this will help you define what answer you want returned by your formula:

    NetDays: Returns the number of days between two dates. Doesn't take in consideration weekends or holidays. Will produce a negative number if date_1 is chronologically after date_2.

    NetWorkDay: Returns the number of working days between two dates. Adds 1 day to the result if the start date is a non-working day.

    NetWorkDays: Returns the number of working days between two dates. Optionally excludes holidays outlined in a range.

    Hope this helps!

  • SJTA
    SJTA ✭✭✭✭
    Options

    I know how they are defined.

    Please have a look at the formula to see what I am missing. My issue is the formula is not working, so I am attempting to break it down as simply as possible.

    My formula is clearly incorrect as I have been working on it all day.

    The issue is the application of the IF, AND, and ISBLANK.

    If Cell A is BLANK

    AND CellB has specific text

    How many NETDAYS between the days in Cells C and D?

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Hi @SJTA,

    If you don't mind what the text value in CellB is:

    =IF(AND(CellA@row = "", ISTEXT(CellB@row)), NETDAYS(CellC@row, CellD@row))

    If you want CellB to be a particular value:

    =IF(AND(CellA@row = "", CellB@row = "Your text here"), NETDAYS(CellC@row, CellD@row))

    Either of these should work - if you're getting an error, what is it?

  • SJTA
    SJTA ✭✭✭✭
    Options

    Hi @Nick Korna

    Thanks for the help.

    The Formula now WORKS!!! Thank you very much!!

    Cell B is going to be options from the approval status down menu (Submitted to Client, Comments Received, Comments Resolved, etc.).

    One day I will figure out why the ISBLANK did not work.

    😀

    -Camille

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    Options

    Your formula will have been given errors due to some missing square brackets on the column references. This should work if you wanted ISBLANK instead:

    =IF(AND(ISBLANK([CellA]@row), [CellB]@row = "All Comments Resolved"), NETDAYS([CellC]@row, [CellD]@row))

    The missing brackets were on the ends of CellA & CellB, which stopped your formula from functioning properly.

  • SJTA
    SJTA ✭✭✭✭
    Options

    @Nick Korna

    YES!! My ISBLANK is now working.

    Thanks again 😀

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Options

    No problem, glad it's all resolved and you have some options to pick from! 😀

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!