Manually enter dates in a column that has a formula?

Deborah Kramer

Hi, I created a formula (with SS Community's help- thank you) for a date column that works great for what it was intended for. However, I now realize that date column also needs to be able to be changed manually. Is that possible - to have both a formula create a date but also be able to enter one manually?

I saw some talk about "helper columns", but I don't understand them or know if they can help in my case.

Here is my situation. We are a health insurance plan onboarding new practitioners to our network.

1) If they are joining a physician group that is already contracted with us, then their effective date is the date that they are approved for credentialing. This is the scenario I created the formula for.

2) However, if they or their group is not yet contracted with us, then it depends on when that contract is fully executed. And even then, that effective date can vary. So this date needs to be entered manually.

This is my formula for new practitioners joining with existing contracted groups. It copies the credentialing approval date to the effective date column.

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

So right now the second row copied that credentialing approval date to the effective date column. However, for the top row - once that contract is fully executed, they can't manually enter the effective date because of the Effective date column formula. Is there a workaround so we can keep the formula but also be able to enter manual dates for any newly contracted providers?

Thank you!

KPH

Hi @Deborah Kramer

You can add another column to manually enter the date for an existing contract and then adapt your IF statement to pull that into your effective date column.

This is the IF you are currently using

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

If the logic is true, [Credentialing status: As of date] is inserted.

If the logic is false, nothing is inserted, but you can change that. You just need to add a comma and whatever you want, at the end, before the closing parenthesis.

So, if you want the date in the column you created called "Manual Date" to appear whenever the logic is false, you can add this bold part

=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, [Manual Date]@row)

You could also put another IF statement in here, instead. For example, to only pull in the Manual Date if the Contract Status is Contract Request Sent and put nothing in for other statuses, you could use

IF([Contract Status]@row = "Contract Request Sent", [Manual Date]@row)

like this

=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, IF([Contract Status]@row = "Contract Request Sent", [Manual Date]@row) )

KPH

Hi @Deborah Kramer

You can add another column to manually enter the date for an existing contract and then adapt your IF statement to pull that into your effective date column.

This is the IF you are currently using

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

If the logic is true, [Credentialing status: As of date] is inserted.

If the logic is false, nothing is inserted, but you can change that. You just need to add a comma and whatever you want, at the end, before the closing parenthesis.

So, if you want the date in the column you created called "Manual Date" to appear whenever the logic is false, you can add this bold part

=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, [Manual Date]@row)

You could also put another IF statement in here, instead. For example, to only pull in the Manual Date if the Contract Status is Contract Request Sent and put nothing in for other statuses, you could use

IF([Contract Status]@row = "Contract Request Sent", [Manual Date]@row)

like this

=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, IF([Contract Status]@row = "Contract Request Sent", [Manual Date]@row) )

Deborah Kramer

I love this community! Thank you, I created a column called "Contract Effective Date" (that's my manual date) and hid it on the main sheet. I included it in the report that the Contract team is working off of, so they can enter that date. Then I added ,[Contract Effective Date]@row to the end of my Effective Date formula on the main sheet and voila!

Is there any way to copy a date from one cell to another using automations? I only see a way to add the current date.

Thanks again!

KPH

Great! Glad it works for you.

With your second question, maybe post it as a new thread, with a bit more detail about what you want to do. My immediate answer is "no", but if you can show what you are wanting to do, that could become a "yes" with some additional steps.

Deborah Kramer

Thank you

Deborah Kramer

Ok, I have a follow up to my above query. Just when you think you've got it figured out, you realize you're missing something! 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 rendering 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 on the contract report.

However, I did not factor in the rendering providers 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 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 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 renderings? The rule is this:

IF they are already approved on the date the 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).

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

Hi Deborah

I was working on an answer here but see another copy of the same question so I am going to jump over to that one.

Anyone else helping Deborah - let's move to here

https://community.smartsheet.com/discussion/113805/complicated-formula-for-date#latest