Status Column Formula

Canteaug

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.

Nic Larsen

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

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

Canteaug

Still having trouble @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")))

Canteaug

still not changing the status to denied once a denial reason is entered. @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.

Canteaug

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

Canteaug

@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

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

Canteaug

That worked! Thank you so much! @Nic Larsen

Canteaug

@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

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

Canteaug

It is saying unparseable. @Nic Larsen

Nic Larsen

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

Canteaug

It seems like everything is matching. @Nic Larsen