IF/THEN/Else with Dates

KSellers
KSellers ✭✭
edited 11/06/23 in Formulas and Functions

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

Tags:

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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)

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

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

  • KPH
    KPH ✭✭✭✭✭✭

    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.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 11/07/23

    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.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!