Forum Discussion

ClariceDennison's avatar
ClariceDennison
Qrew Trainee
3 years ago

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...
Formulas and functions
MarkShnier__You's avatar
MarkShnier__You
Qrew Champion
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's avatar
ClariceDennison
Qrew Trainee
3 years ago
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:

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's avatar
    MarkShnier__You
    Qrew Champion
    3 years ago
    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's avatar
      ClariceDennison
      Qrew Trainee
      3 years ago
      Using Case:




      If I just have this, it works though:

      [Start Date]+Days(3)


      Thanks!

      ------------------------------
      Clarice Dennison
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Qrew Champion
        3 years ago
        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. 

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