Help fixing this formula

PeterM

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!

Paul Newcome

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

Paul Newcome

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

@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

What error are you getting?

PeterM

@Paul Newcome

#UNPARSEABLE

Paul Newcome

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

@Paul Newcome

Same error message.

Paul Newcome

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

PeterM

@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

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