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

Find more posts tagged with

Formulas

Accepted answers

All comments

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)

KSellers

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

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

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.