Forum Discussion

JenniferPeterso's avatar
JenniferPeterso
Qrew Member
6 years ago

Deadline Formulas (Checkbox)

I am trying to write a formula for a checkbox field that will return a check mark when a report was issued before the contract deadline: If([Report Posted], true and Today()>[MSO-Client Due Date], ...
Formulas and functions
DonLarson's avatar
DonLarson
Qrew Elite
6 years ago
Jennifer,

What data is available about the Report?   Are you capturing the date that the report is posted?  If you have that information in the application this is very straightforward.

I am going to assume that all the data is in a single table like this.


Report Posted is a Formula Check Box.  This tells you if the Report has posted, regardless of when.
If ( IsNull([Report Post Date])=false, true, false)

Report on Time is a Formula Check Box which tells you if it was On Time.
If ( [Report Posted]=false, false,
      [Report Post Date]<=[MSO-Client Due Date], true. false)
We just checked if the report exist, if no then we are not on time, if the report exists we are comparing the Post Date to the Due Date.

Report Late is a Formula Check Box which tells you if the Report was or is  late.
If ( [Report Posted]=false  AND Today()> [MSO-Client Due Date], true,
     [Report Post Date]>[MSO-Client Due Date], true, false)

We checked if the report does not exist and if we are past the Due Date,   then we check if the report was posted after the Due Date.

Capturing the Report Post Date can be useful for lots of other business purposes. 

How late it is ?   
What is the average number of days we are late?
What is the longest period of time that it was late?





     




------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------