Discussions

Expand all | Collapse all

Deadline Formulas (Checkbox)

  • 1.  Deadline Formulas (Checkbox)

    Posted 01-23-2020 19:37
    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
    ------------------------------


  • 2.  RE: Deadline Formulas (Checkbox)

    Posted 01-23-2020 19:57
    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
    ------------------------------



  • 3.  RE: Deadline Formulas (Checkbox)

    Posted 01-23-2020 21:06
    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
    ------------------------------