Skip to main content
Question

Past due check box Formula

  • March 22, 2022
  • 0 replies
  • 94 views
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
------------------------------
This topic has been closed for replies.

SSNJeffP
Forum|alt.badge.img+11
  • SSNJeffP
  • Registered
  • March 22, 2022

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

Terms & ConditionsAccessibility statement