Status Column Formula

What I need: If a date is entered into the [HR Review Date] change the [Status] to "Pending HR Review"

If the date entered in the [More Info Requested Date] greater than or equal to the [HR Review Date] change the status to "More Info Needed"

and or if the date entered in the [HR Determination Date] is greater than or equal to the [HR Review Date] or the [More Info Requested Date] change the status to “Denied”


Formula: =IF(ISBLANK([HR Review Date]@row), "", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", IF(AND([HR Determination Date]@row >= [HR Review Date]@row, [HR Determination Date]@row >= [More Info Requested Date]@row, NOT(ISBLANK([Denial Reason]@row))), "Denied", "Pending HR Review")))


Challenge: The formula is doing what I need it to do until I get to the denied piece. It wont update the status to denied unless a date is not entered into the More Info Requested Date Column. Please help. I need to the status to update rather a date is in the More Infor Requested Date column is blank or not.

Best Answer

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    One other version where it will go to Denied if the other dates aren't filled in, but there is something in the Denied column

    =IF(ISBLANK([HR Review Date]@row), "", IF(AND(OR([HR Determination Date]@row >= [More Info Requested Date]@row, [HR Determination Date]@row >= [HR Review Date]@row), NOT(ISBLANK([Denial Reason]@row))), "Denied", IF(NOT(ISBLANK([Denial Reason]@row)), "Denied", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", "Pending HR Review"))))

«1

Answers

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    If it mostly works, I would start by rearranging the order. It will stop whatever becomes true first, so use your 3rd argument first, and then go back from there.

    =IF(AND([HR Determination Date]@row >= [HR Review Date]@row, [HR Determination Date]@row >= [More Info Requested Date]@row, NOT(ISBLANK([Denial Reason]@row))), "Denied", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", IF(ISBLANK([HR Review Date]@row), "", "Pending HR Review")))

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Try: =IF(ISBLANK([HR Review Date]@row), "", IF(AND([HR Determination Date]@row >= [HR Review Date]@row, [HR Determination Date]@row >= [More Info Requested Date]@row, NOT(ISBLANK([Denial Reason]@row))), "Denied", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", "Pending HR Review")))

  • still not changing the status to denied once a denial reason is entered. @Nic Larsen

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    That's because you don't have a date in the column "more info requested" which one of the conditions.

    IF(AND([HR Determination Date]@row >= [HR Review Date]@row, [HR Determination Date]@row >= [More Info Requested Date]@row, NOT(ISBLANK([Denial Reason]@row))), "Denied"

    All of these have to be true to = denied.

    We'll need to split up what can equal denied.

  • So it can be either or. To be denied we wont always have to request more info. @Nic Larsen

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    One more try - a couple competing conditions that make it a bit more challenging.

    =IF(ISBLANK([HR Review Date]@row), "", IF(AND(OR([HR Determination Date]@row >= [More Info Requested Date]@row, [HR Determination Date]@row >= [HR Review Date]@row), NOT(ISBLANK([Denial Reason]@row))), "Denied", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", "Pending HR Review")))

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    Answer ✓

    One other version where it will go to Denied if the other dates aren't filled in, but there is something in the Denied column

    =IF(ISBLANK([HR Review Date]@row), "", IF(AND(OR([HR Determination Date]@row >= [More Info Requested Date]@row, [HR Determination Date]@row >= [HR Review Date]@row), NOT(ISBLANK([Denial Reason]@row))), "Denied", IF(NOT(ISBLANK([Denial Reason]@row)), "Denied", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", "Pending HR Review"))))

  • That worked! Thank you so much! @Nic Larsen

  • @Nic Larsen

    Hi Nic! I have a few more status changes that I could use your expertise on.

    I would also like to be able to automate the status to:


    "HR Accepted Request" once a Leave Start Date is entered,


    If an Extension Requested Date is entered change Status to Extension Requested,


    If an Extension Decision is Yes, w/ documentation, change status to Extension Accepted,


    and If and Extension is "No longer qualifies for FMLA", change status to Extension Denied.


    Can you help with this also?

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭

    Test this out and see if it works:

    =IF(AND(ISDATE([Extension Requested Date]@row), [Extension Decision]@row = "No longer qualifies for FMLA"), "Extension Denied", IF(AND(ISDATE([Extension Requested Date]@row), [Extension Decision]@row = "Yes, w/ documentation"), "Extension Approved", IF(ISDATE([Extension Requested Date]@row), "Extension Requested", IF(ISDATE([Leave Start Date]@row), "HR Accepted Request", IF(ISBLANK([HR Review Date]@row), "", IF(AND(OR([HR Determination Date]@row >= [More Info Requested Date]@row, [HR Determination Date]@row >= [HR Review Date]@row), NOT(ISBLANK([Denial Reason]@row))), "Denied", IF(NOT(ISBLANK([Denial Reason]@row)), "Denied", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", "Pending HR Review"))))))))

  • It is saying unparseable. @Nic Larsen

  • Nic Larsen
    Nic Larsen ✭✭✭✭✭✭
    edited 03/22/24

    Double check the column names match how you have them typed and match your drop down lists, date column are setup as date columns, etc... you may need to retype the formula. I've copied and pasted directly from my sheet to here again just to be safe. Plus screenshot below.

    =IF(AND(ISDATE([Extension Requested Date]@row), [Extension Decision]@row = "No longer qualifies for FMLA"), "Extension Denied", IF(AND(ISDATE([Extension Requested Date]@row), [Extension Decision]@row = "Yes, w/ documentation"), "Extension Approved", IF(ISDATE([Extension Requested Date]@row), "Extension Requested", IF(ISDATE([Leave Start Date]@row), "HR Accepted Request", IF(ISBLANK([HR Review Date]@row), "", IF(AND(OR([HR Determination Date]@row >= [More Info Requested Date]@row, [HR Determination Date]@row >= [HR Review Date]@row), NOT(ISBLANK([Denial Reason]@row))), "Denied", IF(NOT(ISBLANK([Denial Reason]@row)), "Denied", IF([More Info Requested Date]@row >= [HR Review Date]@row, "More Info Needed", "Pending HR Review"))))))))



  • It seems like everything is matching. @Nic Larsen


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!