Help fixing this formula

PeterM

Note: If the pre-closing draw date submitted to strike Team is blank return ''01_Strike Team Needed', 'If the pre-closing draw date submitted to QAQC is blank return ''02_QAQC Needed'', 'If the pre-closing draw date submitted to TIGR is blank return ''03_TIGR Needed'', 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 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 is greater than today then return ''07_In Progress'', if not then return "08_Competed, if the pre-closing draw date submitted to QAQC is greater than today then return ''07_In Progress'', if not then return "08_Competed,if the pre-closing draw date submitted to TIGR is greater than today then return ''07_In Progress'', if not then return "08_Competed,if the pre-closing draw date submitted to Strike Team & if the pre-closing draw date submitted to QAQC & if the pre-closing draw date submitted to TIGR is greater than today then return ''All Draws Compete'', if not then return "Not Compete"

=IF(ISBLANK[Pre-Closing Draw DATE Submitted to Strike Team]@row),"01_Strike Team Needed", =IF(ISBLANK([DATE Pre-Closing Draw Submitted to QAQC]@row), "02_QAQC Needed’’, =IF(ISBLANK([DATE Pre-Closing Draw In TIGR]@row),"03_TIGR Needed’’,= IF(ISTEXT ([Pre-Closing Draw DATE Submitted to Strike Team]@row),"N/A", IF(ISTEXT ([DATE Pre-Closing Draw Submitted to QAQC]@row),"N/A’’, IF(ISTEXT ([DATE Pre-Closing Draw In TIGR]@row),"N/A’’, IF([Pre-Closing Draw DATE Submitted to Strike Team]@row > TODAY(), "07_In Progress","08_Competed", IF([DATE Pre-Closing Draw Submitted to QAQC]@row > TODAY(), "07_In Progress","08_Competed", IF([DATE Pre-Closing Draw In TIGR]@row> TODAY(),"07_In Progress","08_Competed", IF([Pre-Closing Draw DATE Submitted to Strike Team]@row, ([DATE Pre-Closing Draw Submitted to QAQC]@row, ([DATE Pre-Closing Draw In TIGR]@row < TODAY(),"09_Not Compete", "10_All Draws Competed"))))))))))

Kelly Moore

Hello @PeterM

Try this

=IF(ISBLANK([Pre-Closing Draw DATE Submitted to Strike Team]@row), "01_Strike Team Needed", IF(ISBLANK([DATE Pre-Closing Draw Submitted to QAQC]@row), "02_QAQC Needed", IF(ISBLANK([DATE Pre-Closing Draw In TIGR]@row), "03_TIGR Needed", IF(OR(ISTEXT([Pre-Closing Draw DATE Submitted to Strike Team]@row), ISTEXT([DATE Pre-Closing Draw Submitted to QAQC]@row), ISTEXT([DATE Pre-Closing Draw In TIGR]@row)), "N/A", IF(AND([Pre-Closing Draw DATE Submitted to Strike Team]@row > TODAY(), [DATE Pre-Closing Draw Submitted to QAQC]@row > TODAY(), [DATE Pre-Closing Draw In TIGR]@row > TODAY()), "10_All Draws Competed", IF(AND([Pre-Closing Draw DATE Submitted to Strike Team]@row < TODAY(), [DATE Pre-Closing Draw Submitted to QAQC]@row < TODAY(), [DATE Pre-Closing Draw In TIGR]@row < TODAY()), "09_Not Compete", IF(OR([Pre-Closing Draw DATE Submitted to Strike Team]@row > TODAY(), [DATE Pre-Closing Draw Submitted to QAQC]@row > TODAY(), [DATE Pre-Closing Draw In TIGR]@row > TODAY()), "07_In Progress", "08_Competed")))))))

Does this work for you?

Kelly

Kelly Moore

Hello @PeterM

Try this

=IF(ISBLANK([Pre-Closing Draw DATE Submitted to Strike Team]@row), "01_Strike Team Needed", IF(ISBLANK([DATE Pre-Closing Draw Submitted to QAQC]@row), "02_QAQC Needed", IF(ISBLANK([DATE Pre-Closing Draw In TIGR]@row), "03_TIGR Needed", IF(OR(ISTEXT([Pre-Closing Draw DATE Submitted to Strike Team]@row), ISTEXT([DATE Pre-Closing Draw Submitted to QAQC]@row), ISTEXT([DATE Pre-Closing Draw In TIGR]@row)), "N/A", IF(AND([Pre-Closing Draw DATE Submitted to Strike Team]@row > TODAY(), [DATE Pre-Closing Draw Submitted to QAQC]@row > TODAY(), [DATE Pre-Closing Draw In TIGR]@row > TODAY()), "10_All Draws Competed", IF(AND([Pre-Closing Draw DATE Submitted to Strike Team]@row < TODAY(), [DATE Pre-Closing Draw Submitted to QAQC]@row < TODAY(), [DATE Pre-Closing Draw In TIGR]@row < TODAY()), "09_Not Compete", IF(OR([Pre-Closing Draw DATE Submitted to Strike Team]@row > TODAY(), [DATE Pre-Closing Draw Submitted to QAQC]@row > TODAY(), [DATE Pre-Closing Draw In TIGR]@row > TODAY()), "07_In Progress", "08_Competed")))))))

Does this work for you?

Kelly