IF Formula Assistance
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 Modificatio...
Forum Discussion
Thanks Mark!
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:
Thanks!!
------------------------------
Clarice Dennison
------------------------------
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
Qrew Legend
Qrew LegendFormula 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
------------------------------
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
- Using Case:
If I just have this, it works though:
[Start Date]+Days(3)
Thanks!
------------------------------
Clarice Dennison
------------------------------- MarkShnier__YouThe 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.
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 hyphen
var 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.
Case ($Choice,
0, [Start Date]+Days(3),
1, [Start Date]+Days(7),
2, [Start Date]+Days(14),
3, [Start Date]+Days(14),
4, [Start Date]+Days(28),
5, [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, [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
------------------------------Because the Target Date field was originally just a date field and it's being used in other areas, I didn't want to change it to an formula field. So I created Targ Date as a formula field. I then created a dynamic rule to pull the date from the Targ Date field to update the Target Date field.
Apologies, I didn't capture the top of the form (Credentialing Standardization Tasks.) I pulled the exact verbiage from the Credentialing Standardization Tasks drop-down field.
I will try the formula suggested.
Thanks!
------------------------------
Clarice Dennison
------------------------------