IF Statement

eganji

How can I combine the following two formulas in one statement:

1)    IF([Patient Date of Birth]1 OR [Last Patient Visit Date]1) = BLANK OR NULL)

    THEN  ([Patient Date of Birth]1 AND [Last Patient Visit Date]1) = "1/1/1940"

2)    IF(NETDAYS([Patient Date of Birth]1, [Last Patient Visit Date]1) / 360 > 12, DATE(YEAR([Last Patient Visit Date]1) + 7, MONTH([Last Patient Visit Date]1), DAY([Last Patient Visit Date]1)), DATE(YEAR([Patient Date of Birth]1) + 19, MONTH([Patient Date of Birth]1), DAY([Patient Date of Birth]1))).

what I am trying to do is if that if first statement is true and happens, then replace the values in the second statement with "1/1/1940" and continue to the calculation is statement 2.

Thank you

 

Andreas S.

Hi Eganji,

I think what you are trying to archive is not possible. You can´t change the value of a different cell with a formular, you can only calculate the value of a cell from other cells.

But what I guess you need will be possible. 

My understanding of your description is

1. You have a column [PatientDateofBirth] which can have a real value or be empty

2. You have a column [LastPatientVisitDate] which can have a real date or be empty

Now I start to guess:

1. Do you want to plan a next visit in maybe a third column [NextVisit]?

2. If you know the birthday - you want to see the patient first time when he/she is 19 years old?

3. If you have seen the patient once - want to see again after 7 years?

Furthermore I am totally unsure what to do with the 12 years.

Maybe you can explain what you want to archive without the use of formulars first. Then we can maybe give you formulars which will do what you need.

Kind regards

Andreas

 

Andrée Starå

Hi,

A third-party service/integration like Zapier would probably be a great option for this scenario. Is that an option for you?

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

eganji

Hi Andreas,

This is what I need to do: There are patient paper charts/records sitting in closets that need to be purged and destroyed when the following  conditions are  met:

1. Patient must be at least 19 years of age AND 7 yeas must have passed since their last doctor visit
2. If the patient is minor, we will wait until he reaches 19 and again there has to be a 7 year gap since last visit before the he will be eligible for destruction .This part can be done by the formula below:

=IF(NETDAYS([Patient Date of Birth]2, [Last Patient Visit Date]2) / 360 > 12, DATE(YEAR([Last Patient Visit Date]2) + 7, MONTH([Last Patient Visit Date]2), DAY([Last Patient Visit Date]2)), DATE(YEAR([Patient Date of Birth]2) + 19, MONTH([Patient Date of Birth]2), DAY([Patient Date of Birth]2)))

Now this is the twist: A patient can have up to 5 siblings who will each have their own date of birth. Siblings may or may not have ever been visited by a doctor. Hence,siblings all have date of birth and possible last doctor visit date.

If there are siblings, I need to calculate each siblings personal Destruction Due Date. The ultimate and final destruction due date will be the latest between all the ones calculated so far.This ultimate collective date could have been in the past which makes it available for destruction today.Or it can also be in the future. 

I have all the little due dates calculated and a MAX function is identifying the latest due date but when a sibling doesn’t have a  visit date formulas generates a #Invalid Datatype error and because of that the ultimate date of destruction generates the same error message.

If it is OK with you, I like to share my sheet with you to give you a broader view of what I am trying to do.

Thank you

eganji

Hi Andree,

 

I wish third party solutions were options for us. I work for a local government agency where purchasing are all planned through strategic plannings are take a long time. Way too many red tapes. I will check and see what the application is.

Thank you.

Andrée Starå

Happy to help!

Red tapes can be a struggle! 

I'd be happy to take a look at the sheet and see if I can help in any way. (Delete/replace any confidential/sensitive information before sharing)

Andrée Starå

Glad we got it working!

Have a fantastic weekend!

Best,

Andrée