Forum Discussion

TaraStroup's avatar
TaraStroup
Qrew Member
3 years ago

Checkbox formula with multiple dates to refrence

Hello, 

I need to have three date fields
Sunday date
Departure Date 
Arrival Date

I need a checkbox to be true if the Sunday date is within the Arrival and Departure even if Arrival or departure is null.

And False if the Sunday date is prior to Arrival or after Departure

Any thoughts on the formula? 

Thanks,
Tara

------------------------------
Tara Stroup
------------------------------
Formulas and functions

4 Replies

Sort By
  • JeffPeterson1's avatar
    JeffPeterson1
    Qrew Captain
    3 years ago
    Hi Tara,

    Make a formula checkbox field, and assuming these are all 'Date' fields,  this formula:

    [SundayDate] <= [Departure] and [SundayDate] >= [Arrival]
    or
    IsNull([Arrival]) or IsNull([Departure])

    ------------------------------
    Jeff Peterson
    ------------------------------
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    3 years ago
    I usually just write the conditions it can be TRUE, so:

    ([Sunday Date]>[Arrival Date] and [Sunday Date]<[Departure Date]) or ([Sunday Date]>[Arrival Date] and isnull([Departure Date]) or ([Sunday Date]<[Departure Date] and isnull([Arrival Date])

    There are probably multiple ways you can write this. Someone may have a suggestion that is 'cleaner'.

    *Note, Jeff and I were responding at the same time. I am not sure if you mean, 'if either the arrival or departure date is null, then true' OR if you mean 'if the departure date is null, but still check the arrival vs schedule per rules'

    ------------------------------
    Mike Tamoush
    ------------------------------
    • JeffPeterson1's avatar
      JeffPeterson1
      Qrew Captain
      3 years ago
      I don't think you need to list off every possible situation in the formula.  The way I did it,  if the date is between the other two, or if one or both fields are null it will be 'true' (checked),  otherwise it's 'false' (not checked).    


      ------------------------------
      Jeff Peterson
      ------------------------------
      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        3 years ago
        Jeff,

        I understood her ask differently. We interpret this scenario differently.

        Sunday Date > Departure date and Arrival Date is null.

        In my rule, that is false, in yours, that is true. I am unclear what she is asking for. Her last statement says Sunday date can't be outside of the other dates so I am a bit confused on the specific ask.

        ------------------------------
        Mike Tamoush
        ------------------------------