Forum Discussion

TaraStroup's avatar
Qrew Member
3 years ago

Checkbox formula with multiple dates to refrence


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? 


Tara Stroup

4 Replies

  • Hi Tara,

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

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

    Jeff Peterson
  • 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
      Qrew Captain
      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
        Qrew Commander

        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