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], true, false)

And another one if the report was issued after the contract deadline:

If([Report Posted], true and Today()<[MSO-Client Due Date], true, false)

It's not returning the correct results, so there must be something wrong with the way I have them written. Can anyone help me out?

------------------------------
Jennifer Peterson
------------------------------
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    np

    If([Report Posted]= true and Today()>[MSO-Client Due Date], true, false)

    but that can also be written with less typing as
    [Report Posted] and Today()>[MSO-Client Due Date]


    And another one if the report was issued after the contract deadline:

    If([Report Posted]= true and Today()<[MSO-Client Due Date], true, false)

    and similar it can also be written

    [Report Posted] and Today()<[MSO-Client Due Date]


    note that for one of them you also should deal with the equals the due date.

    [Report Posted] and Today() <= [MSO-Client Due Date]

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
  • 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
    ------------------------------