Forum Discussion

MatthewJones's avatar
MatthewJones
Qrew Trainee
3 years ago

Past due check box Formula

Good afternoon,
I'm trying to find a way to do a check box formula to make a check box be checked if a date is past due. So I have a certification table that has the employee certification and expiration dates and I am wanting some sort of visual check box to check if one of them is past due. Im wanting it to look at all the dates also. see screen shot below



------------------------------
Matthew Jones
------------------------------
  • So are you just wanting a single checkbox that is checked if any of the dates are expired?   Or do you want a field that calls out visually which ones are expired if there are more than one?

    For the checkbox option,  you'd make a Formula Checkbox field called "Expired Certification" and the formula would be something like:

    If(
    Today()>[Cert Date1] or
    Today()>[Cert Date2] or
    Today()>[Cert Date3]
    ,true)

    You'd need a line for each date field you're checking against, but if any of them are expired it would check the box.

    You could also do something similar with a Formula Rich Text field,  except you could make it call it out the ones that are expired in red or something like that.  

    To do that you'll make a Formula Rich Text field called 'Expired Cert Warnings' or something like that and the code would look like:

    var text CertOne = If(Today()>[Cert 1 Expiration],"<span style='font-size: 14pt; text-align: right; color: Red'>CertOne is expired!.</span>","");
    var text CertTwo = If(Today()>[Cert 2 Expiration],"<span style='font-size: 14pt; text-align: right; color: Red'>CertTwo is expired!.</span>","");

    List("<br>",$CertOne,$CertTwo)

    You'd get this as a result if CertOne is expired:


    or this if both of the 2 above are expired:




    Of course you'd need to add the appropriate lines for each date field.   

    Hope that helps!

    ------------------------------
    Jeff Peterson
    ------------------------------

    • MatthewJones's avatar
      MatthewJones
      Qrew Trainee
      Good morning,
      I am wanting a single checkbox that is checked if any of them is expired. 

      Thanks

      ------------------------------
      Matthew Jones
      ------------------------------
      • JeffPeterson1's avatar
        JeffPeterson1
        Qrew Captain
        OK,  just follow the first part of the instructions in my above post and that will do exactly what you're looking for.

        ------------------------------
        Jeff Peterson
        ------------------------------
    • MatthewJones's avatar
      MatthewJones
      Qrew Trainee
      I have 1 more question on this, if i wanted this to alert 30 days out instead of on today how would i change the string? 

      var text MMC = If(Today()>[MMC EXP],"<span style='font-size: 12pt; text-align: right; color: Red'>MMC.</span>","");

      ------------------------------
      Matthew Jones
      ------------------------------