Help fixing this formula

Options

Note:

If the pre-closing draw date submitted to TIGR has a number, then return ‘’TIGR’’ if not return blank.

If the pre-closing draw date submitted to QAQC has a number, then return ‘’QAQC’’ if not return blank.

If the pre-closing draw date submitted to strike Team has a number, then return ‘’Strike’’ if not return blank.

If the pre-closing draw date submitted to TIGR & pre-closing draw date submitted to QAQC & pre-closing draw date submitted to strike Team has a number, then return ‘’All Completed’’

If the pre-closing draw date submitted to TIGR has a text, then return ''N/A'',

If the pre-closing draw date submitted to Strike Team has a text, then return ''N/A'',

 If the pre-closing draw date submitted to QAQC has a text, then return ''N/A''

 

=IF(ISNUMBER([DATE Pre-Closing Draw In TIGR]@row), "TIGR", IF(ISNUMBER([DATE Pre-Closing Draw Submitted to QAQC]@row), "QAQC", IF(ISNUMBER([Pre-Closing Draw DATE Submitted to Strike Team]@row), "Strike", IF(AND(ISNUMBER([DATE Pre-Closing Draw In TIGR]@row), ISNUMBER([DATE Pre-Closing Draw Submitted to QAQC]@row), ISNUMBER([Pre-Closing Draw DATE Submitted to Strike Team]@row)),''All Completed'', '' '', IF(OR(ISTEXT([DATE Pre-Closing Draw In TIGR]@row,[DATE Pre-Closing Draw Submitted to QAQC]@row,[Pre-Closing Draw DATE Submitted to Strike Team]@row,''N/A'')))))))




Thank you for your help!

Best Answer

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you getting an error or is it outputting something unexpected? I noticed you are using ISNUMBER and ISTEXT to evaluate what I assume are date columns instead of using the ISDATE function.

  • PeterM
    PeterM ✭✭
    Options

    @Paul Newcome

    I'm still receiving an error.

    Note:

    If the pre-closing draw date submitted to TIGR has a date, then return ‘’TIGR’’

    If the pre-closing draw date submitted to QAQC has a date, then return ‘’QAQC’’

    If the pre-closing draw date submitted to strike Team has a date, then return ‘’Strike’’

    If the pre-closing draw date submitted to TIGR & pre-closing draw date submitted to QAQC & pre-closing draw date submitted to strike Team has a date, then return ‘’All Completed’’

    If the pre-closing draw date submitted to TIGR has a text, then return ''N/A'',

    If the pre-closing draw date submitted to Strike Team has a text, then return ''N/A'',

     If the pre-closing draw date submitted to QAQC has a text, then return ''N/A''

     

    =IF(ISDATE([DATE Pre-Closing Draw In TIGR]@row), "TIGR", IF(ISDATE([DATE Pre-Closing Draw Submitted to QAQC]@row), "QAQC", IF(ISDATE([Pre-Closing Draw DATE Submitted to Strike Team]@row), "Strike", IF(AND(ISDATE([DATE Pre-Closing Draw In TIGR]@row), ISDATE([DATE Pre-Closing Draw Submitted to QAQC]@row), ISDATE([Pre-Closing Draw DATE Submitted to Strike Team]@row)),''All Completed'', '' '', IF(OR(ISTEXT([DATE Pre-Closing Draw In TIGR]@row,[DATE Pre-Closing Draw Submitted to QAQC]@row,[Pre-Closing Draw DATE Submitted to Strike Team]@row,''N/A'')))))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    I think I see some syntax issues. Give this a try:

    =IF(ISDATE([DATE Pre-Closing Draw In TIGR]@row), "TIGR", IF(ISDATE([DATE Pre-Closing Draw Submitted to QAQC]@row), "QAQC", IF(ISDATE([Pre-Closing Draw DATE Submitted to Strike Team]@row), "Strike", IF(AND(ISDATE([DATE Pre-Closing Draw In TIGR]@row), ISDATE([DATE Pre-Closing Draw Submitted to QAQC]@row), ISDATE([Pre-Closing Draw DATE Submitted to Strike Team]@row)),''All Completed'', IF(OR(ISTEXT([DATE Pre-Closing Draw In TIGR]@row), ISTEXT([DATE Pre-Closing Draw Submitted to QAQC]@row),ISTEXT([Pre-Closing Draw DATE Submitted to Strike Team]@row),''N/A'')))))

  • PeterM
    PeterM ✭✭
    Options
  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Are you able to provide a screenshot of the formula actually in the sheet similar to the screenshot below?


  • PeterM
    PeterM ✭✭
    Options

    @Paul Newcome

    This is the logic:

    If the pre-closing draw date submitted to TIGR has a date, then return ‘’TIGR’’

    If the pre-closing draw date submitted to QAQC has a date, then return ‘’QAQC’’

    If the pre-closing draw date submitted to strike Team has a date, then return ‘’Strike’’

    If the pre-closing draw date submitted to TIGR & pre-closing draw date submitted to QAQC & pre-closing draw date submitted to strike Team has a date, then return ‘’All Completed’’

    If the pre-closing draw date submitted to TIGR has a text, then return ''N/A'',

    If the pre-closing draw date submitted to Strike Team has a text, then return ''N/A'',

     If the pre-closing draw date submitted to QAQC has a text, then return ''N/A''


    Thank you!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    It looks like it is not registering a column name. Instead of typing them out, click on each cell as needed. I also see that you didn't remove the section after "All Completed" (the first bold change in my example above).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!