IF Statement
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
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 emptyNow 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
-
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.
-
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:
- Patient must be at least 19 years of age AND 7 yeas must have passed since their last doctor visit
- 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
-
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.
-
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)
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!