IF Statement

eganji
eganji ✭✭✭
edited 12/09/19 in Formulas and Functions

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

 

Tags:

Comments

  • 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å
    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

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • eganji
    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
    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å
    Andrée Starå ✭✭✭✭✭✭

    Happy to help!

    Red tapes can be a struggle! wink

    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)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Glad we got it working!

    Have a fantastic weekend!

    Best,

    Andrée

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!