IF/THEN/Else with Dates
Hi All!
I'm needing to create a column date formula for if appointment type = a or b, then Report Due Date = Appt Date + 60; and if Appointment Type = anything else, then report due date = appt date + 30.
Based on answers to other similar questions, here is what I have so far: =IF(APPOINTMENT_TYPE@ROW="SIBTF__INITIAL", [APPT_DATE]@ROW +60, IF(APPOINTMENT_TYPE@ROW=****This is where I am stuck because I do not know how to make this read "if appointment type is anything else. However, it does not work even if I stop before the second "IF", so I must have something fundamentally wrong.
Any help is greatly appreciated!
Thanks in advance!
Kelly
Answers

The initial issue is @row needs to be all lower case. You also need to wrap column names that contain spaces, numbers, and/or special characters in [square brackets].
Give this formula a try:
[APPT_DATE]@row + IF(OR([APPOINTMENT_TYPE]@row = "A", [APPOINTMENT_TYPE]@row = "B"), 60, 30)

Hi Paul,
Thanks! That still isn't working for me though. Any other ideas of what I might be doing wrong? Additionally, I need it to be if A (which is actually "SIBTF  Initial" or "SIBTF  Specialist"), then + 60, but if anything else (there's 10+ dropdown options on the "Appointment Type" column), then +30. Would the current formula be able to do that or was it just differentiating between A vs B.
Appreciate it!

Hi @KSellers
Can you paste your formula so we can see what the issue could be?
WRT the second question, Paul's formula differentiates between A or B and everything else (A or B add 60, everything else add 30). You can replace A with "SIBTF  Initial" and B with "SIBTF  Specialist".
What @Paul Newcome has done for you is to take the APPT_DATE
=[APPT_DATE]@row
then add
=[APPT_DATE]@row +
one of two amounts, either 60 or 30, depending on an IF condition
=[APPT_DATE]@row + IF(I'll come to this next), 60, 30)
the condition that determines which number of days to add, could be based on one appointment type, for example, add 60 if it is type A, and 30 for all others, would be
=[APPT_DATE]@row + IF([APPOINTMENT_TYPE]@row = "A"), 60, 30)
but Paul has added a second option for you, so if it is A or B, 60 days will be added, 30 for all others.
=[APPT_DATE]@row + IF(OR([APPOINTMENT_TYPE]@row = "A", [APPOINTMENT_TYPE]@row = "B"), 60, 30)
I hope that helps.

My formula says
"If it is a or b then add 60 days, otherwise (meaning anything else) add 30 days."
Feel free to plug the formula into a temporary date column to test it after updating "a" and "b" accordingly.
Help Article Resources
Categories
Check out the Formula Handbook template!