Complicated formula for date

Deborah Kramer · 12/02/23

Just when you think you've got it figured out, you realize you're missing something! To me this is complicated, but you guys have been a huge help. Tempted to just go back to manual entry.

We are a health insurance plan onboarding new practitioners to join our network.

So right now I have:

=IF(AND([Contract Status]@row = "Existing Contract", [Credentialing Status]@row = "Approved", OR(Enrollment@row = "DHCS Enrolled", Enrollment@row = "Not Applicable", Enrollment@row = "DHCS Applied")), [Credentialing status: As of date]@row, [Contract Effective Date]@row)

So if a practitioner is joining a physician group that is already contracted with us (existing contract), this formula works. If a solo practitioner or facility is newly contracting with us, this works because the contracting team will enter the contract date manually in another column on the contract report.

However, I did not factor in the practitioners that are part of a newly contracted physician group. Right now we have them in a parent/child relationship on the Smartsheet.

We have been leaving the contract status of the individual practitioners blank, because it's only at the group level that we contract. (I'm thinking I may have to give them some status like "new rendering"). However we do credential the individual rendering practitioners and make sure they are enrolled with DHCS (next 2 status columns). Only the group shows up on the contract report, where they can manually enter the contract effective date.

SO, the question becomes how to enter the effective date of the new rendering practitioners? The rule is this:

IF they are already approved on the date the group contract becomes effective, (FYI: the contract status would be green and read "fully executed"), then their effective date would be the same date as the contract effective date (which was manually entered on the parent row by the contract team on the contract report).

IF they are not yet approved on the date the contract becomes effective, then their effective date would be the date the credentialing was approved.

Is there a way to add all this to my existing formula, or should I just go back to having everyone enter the dates manually?

Thank you! Deborah.

KPH · 12/13/23

The current formula first checks if the row has ancestors. If it does (it is therefore a child) and applies all of the other logic. If it does not then it returns Contract Effective Date - This could be because it is a parent, or alone. We could adapt that first part to first separate those with or without children. Like this:

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, "formula for parents to go here", "Formula for non parents & child rows to go here")

When we include the formula for rows with/without ancestors that we already have, this becomes:

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, "formula for parents to go here", IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Contract Effective Date]@row, IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row)), ""))))

We need to move the Contract Effective Date to become the value returned for parent rows and leave a gap for the old formula to be evaluated where rows do not have children nor parents. Like this:

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row, IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "old formula here", IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row)), ""))))

Pasting in your formula (that I have not read or attempted to understand) results in this:

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row, IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, IF(AND([Contract Status]@row = "Existing Contract", [Credentialing Status]@row = "Approved", OR(Enrollment@row = "DHCS Enrolled", Enrollment@row = "Not Applicable", Enrollment@row = "DHCS Applied")), [Credentialing status As of date]@row, [Contract Effective Date]@row), IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row)), ""))))

This is the step by step breakdown of the revised formula

1. Does the row have children? If so, then return the Contract Effective Date

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row)

2. Does the row have any ancestors?

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "Formula if no ancestors - therefore not a parent or child - goes here", "Formula for rows with parents i.e. children goes here")

This combined with the formulas created above becomes

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row,IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "Formula if no ancestors", "Formula for rows with parents i.e. children goes here"))

3. Does the child have a contract effective date? If so, return nothing

=IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", "formula for if not blank")

This combined with the formulas created above becomes

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row,IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "Formula if no ancestors", IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", "formula for if not blank")))

4. Is credentialing status "approved"? If not return nothing

=IF([Credentialing Status]@row = "Approved", "formula for if approved", "")

This combined with the formula created above becomes

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row,IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "Formula if no ancestors", IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", "formula for if approved", ""))))

5. Is the child's credentialing status: as of date later than parent's contract effective date? If so, return child's credentialing status: as of date. If not, return parent's contract effective date.

=IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row))

This combined with the formula created above becomes

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row,IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, "Formula if no ancestors", IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row)), ""))))

6. Formula for rows with no ancestors and no children

I just pasted in what you already have

=IF(COUNT(CHILDREN([Primary Column]@row)) > 0, [Contract Effective Date]@row, IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, IF(AND([Contract Status]@row = "Existing Contract", [Credentialing Status]@row = "Approved", OR(Enrollment@row = "DHCS Enrolled", Enrollment@row = "Not Applicable", Enrollment@row = "DHCS Applied")), [Credentialing status As of date]@row, [Contract Effective Date]@row), IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row)), ""))))

KPH · 12/02/23

I just mocked up a copy of your sheet to try to figure this out. Before I look at the new question though, I have a concern about the original formula you have. I know we tweaked the end to return Contract Effective Date if the logic was false. Is it returning the Credentialing status: As of date correctly?

KPH · 12/02/23

I'm going to try to answer your question in parts, and have questions. Let's dig into this bit

1) IF they are already approved on the date the group contract becomes effective, (FYI: the contract status would be green and read "fully executed"), then their effective date would be the same date as the contract effective date (which was manually entered on the parent row by the contract team on the contract report).

When you say "the contract status would be green and read "fully executed"" does that mean we can use that in the formula, or do we need to compare two dates? In other words, we can re-write criteria 1 as

IF the contract status is "fully executed", then their effective date would be the contract effective date.

If so, this formula would do that (we can look at combing it later but I want to show you how it is built and write the formula in smaller pieces so it is human readable)

=IF([Contract Status]@row = "Fully executed", [Contract Effective Date]@row)

2) IF they are not yet approved on the date the contract becomes effective, then their effective date would be the date the credentialing was approved.

So, if the above logic is false, we can put in a different date. Is there s column that holds "the date the credentialing was approved"? Is it the Credentialing status As of date column? If so, you can pop this into the formula above as the thing to return if the logic is true.

=IF([Contract Status]@row = "Fully executed", [Contract Effective Date]@row, [Credentialing status As of date]@row)

Before adding this to your existing formula, I want to pause and check that the formula is doing what you expect (see my question above).

Deborah Kramer · 12/03/23

Hi, thanks so much for responding so quickly! I saw you first saw my follow up question in my last post, but then I wasn't sure if anyone would answer a new question that was a reply to an old post so I posted it again here.

So... I have to talk with our contract team, but I believe that the contract effective date is not always the day it's fully executed. Usually it's the first of the following month, but when expedited then it's the same day as fully executed. So I have a separate column hidden on the main sheet but showing on the contract report where they enter the contract effective date manually. Then it gets pulled into my existing formula.

But here's the issue. In the first screenshot, each row is individually contracted (could be a solo provider, could be a surgical center), but in the second screenshot, we are contracting with the physician group, but we don't contract individually with the doctors that work for that group. That's why it's set up as parent/child on the Smartsheet. We only credential the doctors.

The contract team are only manually entering the contract effective date for the group (the parent row), not for the practitioners working within that group (the indented child rows). So the practitioners' effective dates would be looking at the parent row for the contract effective date. I think it's possible to build a formula for that, I just didn't know if it was possible to add it to my current formula.

Something like...If child row status in credentialing is "approved" and child credentialing as of date is less than contract effective date in parent row, then effective date in child row is same as contract effective date in parent row. Otherwise effective date is credentialing approval date in child row.

I suppose the other option is to not hide the contract effective date on the main sheet, maybe call it something else, and then have us enter the practitioners manually in there. Another option would be to have two effective date columns, each one with it's own formula. I just think it would be confusing to have two effective date columns showing on the main sheet, no matter what I call them.

I hope that's not too confusing! Thanks again for your help!

KPH · 12/05/23

Hi,

Always a good idea to start a new thread with a new question. I added a note to the end of the last thread to direct people here.

Can we do it?

I think it is highly likely that we can create a formula to do what you want. So long as we can define the logic, make sure the logic covers all the situations, and ensure that nothing that is used in the logic is updated by the logic (which would create a circular reference).

If we can explain the logic in a format such as:

IF something and something is true, then use this date

IF not true but something else is true, then use this date

If that is not true but this is or this is, then use this date

Full column names would be really helpful as I'm not sure I always understand the terms used to describe the dates.

Will it be confusing?

In terms of keeping it simple for people filling the sheet, you have a few options.

Forms - might be too simple for your needs but a good way to limit what is seen/entered and add additional help text. You can use logic to make fields appear only when needed. Only good for adding rows not editing.
Hidden columns - hide anything that isn't needed by those entering data.
Lock columns - lock things that should not be edited.
Color coding - I do this a lot - either fixed colors or conditional formatting. My team know to enter only in the white cells. In your case, you might want to also use color to join dates with terms so they are "paired up" if that makes more sense to your users.

Onto a formula

Right now, I can't confidently combine this with your current formula as I'm not sure what your current formula does (every situation I have tried with it is false). But I can start to make the formula that does the new thing and we can add it in (example at the very end).

You are going to need a few parts and I'll explain each one separately.

This IF will identify if the row is a child

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, 1)

We can then replace the 1 in bold with the thing to do for Children rows.

This will copy the Contract Effective Date from the parent row (which is one of the outcomes we want)

=PARENT([Contract Effective Date]@row)

This will evaluate your logic

=IF(AND([Credentialing Status]@row = "Approved", [Credentialing status As of date]@row < PARENT([Contract Effective Date]@row)), "true", "false")

I have made some assumptions in this area.

You asked for

If child row status in credentialing is "approved" and child credentialing as of date is less than contract effective date in parent row, then effective date in child row is same as contract effective date in parent row. Otherwise effective date is credentialing approval date in child row.

I understood that to mean:

You need a formula for the Effective Date column so that if

row is a Child
and Credentialing Status = "Approved"
and Credentialing status As of date is less than parent Contract Effective Date
then return parent Contract Effective Date.
If not return Child Credentialing status As of date.

If I have any of this wrong, let me know.

When all the parts are put together the formula is

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status As of date]@row < PARENT([Contract Effective Date]@row)), PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row))

You can combine this with a formula for rows that are not children by placing the other formula at the end (this logic will be evaluated if the row is not a child row)

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status As of date]@row < PARENT([Contract Effective Date]@row)), PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row), Formula for non-child rows)

For example:

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status As of date]@row < PARENT([Contract Effective Date]@row)), PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row), IF(AND([Contract Status]@row = "Existing Contract", [Credentialing Status]@row = "Approved", OR(Enrollment@row = "DHCS Enrolled", Enrollment@row = "Not Applicable", Enrollment@row = "DHCS Applied")), [Credentialing status: As of date]@row, [Contract Effective Date]@row))

Deborah Kramer · 12/06/23

Wow, that is one heavy lift! I will try it and let you know how it turns out. Thank you!

Deborah Kramer · 12/10/23

So it said that the syntax wasn't quite right. I'll look at it more carefully and see if it makes sense to my feeble brain!

Ok, so I got as far as the second to last part and it was working correctly except for one thing. If credentialing status is not approved, it was still coming back with the Credentialing Status: As of date.

Here's a made-up sample of my sheet. The first two rows I used my original existing formula. So the first line is a facility that we would contract with. The enrollment docs are inadequate and it's a new contract that isn't executed yet so there has been no date entered in the contract effective date column (which is usually hidden here only showing on contract report) and therefore no date in the Effective Date column. The second line is a therapist, Mr. Rogers, joining a group that is already contracted with us ("existing contract"), therefore it calculated his effective date to match his credentialing approval date.

Then we get to a new contract with a new orthopedic group that has 3 practitioners working for it. This is where I tried the new formula. We contract at the group level and credential at the practitioner level. The practitioners can't be effective in our network until the group contract is in place. They also can't be effective until we've verified their credentials and approved them. That's why the later of the two dates is their effective date.

I tried to fix it but it's not coming out right. Here's what I tried:

=IF(COUNT(ANCESTORS([Last, First Name / Org Name]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status: As of date]@row < PARENT([Contract effective date]@row)), PARENT([Contract effective date]@row), IF(AND([Credentialing Status]@row = "Approved", [Credentialing status: As of date]@row > PARENT([Contract effective date]@row), [Credentialing status: As of date]@row, [Contract effective date]@row))))

Until I get this part right I'm not going to try to join it with my original formula. Thanks for your help, I hope that's clear!

KPH · 12/11/23

Hi @Deborah Kramer

The formula is returning the expected results but the logic used in the formula is not what you want.

This was the logic

row is a Child
and Credentialing Status = "Approved"
and Credentialing status As of date is less than parent Contract Effective Date
then return parent Contract Effective Date.
If false return Child Credentialing status As of date.

In the case of Mr Smith this means

row is a Child = TRUE
and Credentialing Status = "Approved" = FALSE
If false return Child Credentialing status As of date.

Can you explain, using the column names and values, what should happen if Credentialing Status is not "Approved" or Credentialing status As of date is greater than parent Contract Effective Date?

Is it that rather than Child Credentialing status As of date you want to use the later of Child Credentialing status As of date and Parent Contract Effective Date?

Deborah Kramer · 12/11/23

Hi KPH,

So, in order to have an effective date, the child has to be approved for credentialing AND their group contract (parent) has to be effective. So yes, their effective date would the later of the two.

The problem is that we're updating the credentialing status as we go with a credentialing status "as of" date. So Mr. Smith is not yet approved, but he is "in process" as of 11/4/23. I realize what the formula is doing, I'm just not sure how to fix it. Mr. Smith can't be effective until he is approved AND Summerland Orthopedics has a contract effective date. So there shouldn't be any date showing in the effective date column until he is approved.

This is the equation that DOES work for Dr. Jones and Mr. Wilson as long as there is a contract effective date and they are approved.

=IF(COUNT(ANCESTORS([Last, First Name / Org Name]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status: As of date]@row < PARENT([Contract effective date]@row)), PARENT([Contract effective date]@row), [Credentialing status: As of date]@row), [Contract effective date]@row)

I tried another way to fix it, for Mr. Smith, but it didn't work either.

=IF(AND(COUNT(ANCESTORS([Last, First Name / Org Name]@row)) > 0, [Credentialing Status]@row = "Approved"), IF([Credentialing status: As of date]@row < PARENT([Contract effective date]@row)), PARENT([Contract effective date]@row), [Credentialing status: As of date]@row, [Contract effective date]@row)

Now I'm realizing that it's not accounting for there not being a contract effective date either. If there is no contract effective date, then there shouldn't be any effective date for any of the 3 "children", but there is.

Oh, so complicated! I apologize!

Deborah Kramer · 12/11/23

Does this break it down better?

Is this a child?
Does the parent have a contract effective date?
Is the child's credentialing status "approved"?
If yes to all above, then the child's effective date is the later of the parent's contract effective date or the credentialing status: as of date.
If either 2 or 3 is false, then there should be no effective date for the child until both are true.
If not a child, (a parent) then the effective date is the contract effective date.

KPH · 12/12/23

That does help a lot. The formula we have covers 1-4

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status As of date]@row < PARENT([Contract Effective Date]@row)), PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row))

To make this work for situation 5 we remove the part in bold. Originally this was "Otherwise effective date is credentialing approval date in child row. " Now it is "Otherwise the cell is blank"

The new formula is

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status As of date]@row < PARENT([Contract Effective Date]@row)), PARENT([Contract Effective Date]@row)))

For step 6 we add in this part

=IF(COUNT(ANCESTORS([Primary Column]@row)) > 0, IF(AND([Credentialing Status]@row = "Approved", [Credentialing status As of date]@row < PARENT([Contract Effective Date]@row)), PARENT([Contract Effective Date]@row)), [Contract Effective Date]@row)

Deborah Kramer · 12/12/23

Hi KPH,

Correct me if I'm wrong, but that doesn't address the situation when the Credentialing Status is approved and the Credentialing Status: As of Date is greater than the PARENT contract date. Then the result should be the Credentialing Status: As of Date.

KPH · 12/13/23

I've re-ordered some of the logic and split some of the conditions up into separate IFs rather than using AND to make this easier to adjust and understand.

Here it is, step by step:

1. If the row is a parent then return the Contract Effective Date

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Contract Effective Date]@row, "Formula for if not to go here")

2. Does the parent have a contract effective date? If so return nothing

=IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", "formula for if not blank")

This combined with the formula created above becomes

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Contract Effective Date]@row, IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", "formula for if not blank"))

3. Is credentialing status "approved"? If not return nothing

=IF([Credentialing Status]@row = "Approved", "formula for if approved", "")

This combined with the formula created above becomes

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Contract Effective Date]@row, IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", "formula for if approved", "")))

4. Is the child's credentialing status: as of date later than parent's contract effective date? If so, return child's credentialing status: as of date. If not, return parent's contract effective date.

=IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row))

This combined with the formula created above becomes

=IF(COUNT(ANCESTORS([Primary Column]@row)) = 0, [Contract Effective Date]@row, IF(ISBLANK(PARENT([Contract Effective Date]@row)), "", IF([Credentialing Status]@row = "Approved", IF([Credentialing status As of date]@row > PARENT([Contract Effective Date]@row), [Credentialing status As of date]@row, PARENT([Contract Effective Date]@row)), "")))

Does that work for you?

Deborah Kramer · 12/13/23

Yes, that worked perfectly!

Don't kill me, but is there a way now to combine that with the other formula I had for when there is no parent/child relationship?

This is my previous formula that is used on the first two rows:

=IF(AND([Contract Status]@row = "Existing Contract", [Credentialing Status]@row = "Approved", OR(Enrollment@row = "DHCS Enrolled", Enrollment@row = "Not Applicable", Enrollment@row = "DHCS Applied")), [Credentialing status: As of date]@row, [Contract effective date]@row)

So, a contract can't be fully executed until the credentialing is approved (See Acme Surgical Center), or in the case of a group like Summerland Orthopedics, until at least one practitioner rendering services for that group is approved for credentialing. But for a new hire joining a group that's already contracted with us (Fred Rogers), there would be no contract effective date entered. They could have been contracted with us for years. So really it's just for "existing contract" that we need to add something in to the formula. We could probably do away with the part about enrollment status because technically we're not supposed to even start credentialing until enrollment status is "DHCS Enrolled", "DHCS Applied" or "Not Applicable". I put that in there because that's a new business rule and so some of the providers on the sheet were already approved before the enrollment piece was in place.

You're so patient with me! So if a provider's contract status is "existing contract" and their credentialing status is "approved", then their effective date is the Credentialing Status: As of date. Can we squeeze that in? :-)

KPH · 12/13/23