IF Formula Assistance

Question

Hi!I'm trying to create IF statements based on if a field is = to X, then based on the selection, I want to populate a specific target date.Example, if "0 - (Optional) - Fast Track Modifications" selected from the Credentialing Standardization Tasks field, then add 3 days to the Targ Date.This is what I have so far on the Targ Date field:If([Credentialing Standardization Tasks] = "0 - (Optional) - Fast Track Modifications", [Start Date]+Days(3))1. Is this possible?2. If it is, am I able to have 16 other IF statements for the Targ Date field? Each of these tasks have different target dates.* I needed to create a Targ Date field (Formula - URL) as the Target Date field was a Date field and is being used in other areas and I'm afraid it unintentionally affect what it's being used for in other areas.* I have a dynamic form rule to populate what's in the Targ Date field, in the Target Date field since I couldn't build (the above) on the Target Date.Thank you!------------------------------Clarice Dennison------------------------------

markshnier__you · Answer

If you want to make a formula field to calculate a Calculated date field, then you can do a single IF and list the tests vertically for readability.
If(
[Credentialing Standardization Tasks] = "0 - (Optional) - Fast Track Modifications", [Start Date]+Days(3),
[Credentialing Standardization Tasks] = "1 - xxxxxxxxxx", [Start Date]+Days(4),
[Credentialing Standardization Tasks] = "0 - yyyyyyyy", [Start Date]+Days(3)
)

IF all of your of your tests are against the same field, then you can also use a Case Function

Case([Credentialing Standardization Tasks],
"0 - (Optional) - Fast Track Modifications", [Start Date]+Days(3),
"1 - xxxxxxxxxx", [Start Date]+Days(4),
"2 - yyyyyyyy", [Start Date]+Days(3)
)

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

claricedennison · Answer

Thanks Mark!&nbsp;Unfortunately, the date in the Targ Date is not populating. Not sure If I'm missing anything. I tried both the IF and Case statements, but both did not work.The Case:
Case([Credentialing Standardization Tasks],
"0 - (Optional) - Fast Track Modifications", [Start Date]+Days(3),
"1 - Kickoff Meeting", [Start Date]+Days(7),
"2 - Pre-Requisite eLearnings", [Start Date]+Days(14),
"3 - Observe Training as a student", [Start Date]+Days(14),
"4 - Observe Training as an instructor", [Start Date]+Days(28),
"5 - Job Shadowing/Site Observations", [Start Date]+Days(28),
"6 - Assessment Phase - Kickoff", [Start Date]+Days(35),
"7 - Assessment Phase - Project (Epic only)", [Start Date]+Days(42),
"8 - Assessment Phase - Project grading (Epic only)", [Start Date]+Days(42),
"9 - Assessment Phase - Train the Trainer practice or Deep Dive", [Start Date]+Days(49),
"10 -Assessment Phase - Train the Trainer", [Start Date]+Days(56),
"11 - Assessment Phase - Train the Trainer grading", [Start Date]+Days(56),
"12 - Assessment Phase - Test (Epic only)", [Start Date]+Days(70),
"13 - Assessment Phase - Test grading (Epic only)", [Start Date]+Days(70),
"14 - Observation Final Sign-off", [Start Date]+Days(84),
"15 - Observation Final Sign-off grading", [Start Date]+Days(84),
"16 - Certificate for Credentialing (Epic only)", [Start Date]+Days(91),
"17 - Update Educators on Workday and QuickBase", [Start Date]+Days(91)
)
Thanks!!------------------------------Clarice Dennison------------------------------

markshnier__you · Answer

Formula looks correct. Can you post a screenshot of a record where the start date is populated and there's a selection in that field for [Credentialing Standardization Tasks] and also show the calculated field being blank?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

claricedennison · Answer

Using Case:If I just have this, it works though:[Start Date]+Days(3)Thanks!------------------------------Clarice Dennison------------------------------

markshnier__you · Answer

The issue is that computers are very picky when something needs to be exactly equal to something else. Equals means equals. The actual text in the screenshot does not correspond to the text in your formula.&nbsp;Can you tell me if that field can be changed to a multiple-choice drop down field? How is it being populated? Either we need to make the formula more tolerant for example checking what the value in that field begins with i.e. does it begins with a 1 or a 2 or 3 or a 10 or an 11, or we need to ensure that your formula matches the text somehow where does that text come from?But you can also try this // grab off the first part of the string until we hit either a space or a hyphenvar number Choice = ToNumber Left([Credentialing Standardization Tasks]," -");// now we have a clean numerical value from 0 to 15 and we use the Case function on it.&nbsp;Case ($Choice,
0, [Start Date]+Days(3),
1, [Start Date]+Days(7),
2, [Start Date]+Days(14),
3,&nbsp; [Start Date]+Days(14),
4, [Start Date]+Days(28),
5,&nbsp; [Start Date]+Days(28),
6, [Start Date]+Days(35),
7, [Start Date]+Days(42),
8, [Start Date]+Days(42),
9, [Start Date]+Days(49),
10, [Start Date]+Days(56),
11,&nbsp; [Start Date]+Days(56),
12, [Start Date]+Days(70),
13, [Start Date]+Days(70),
14,[Start Date]+Days(84),
15, [Start Date]+Days(84),
16, [Start Date]+Days(91),
17, [Start Date]+Days(91)
)
------------------------------Mark Shnier (YQC)mark.shnier@gmail.com------------------------------

Forum Discussion

IF Formula Assistance