Skip to main content
Question

Checkbox formula with multiple dates to refrence

  • December 28, 2021
  • 0 replies
  • 173 views
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
------------------------------

SSNJeffP
Forum|alt.badge.img+11
  • SSNJeffP
  • Quickbase Alumni
  • December 28, 2021
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
------------------------------
Forum|alt.badge.img+20
  • MikeTamoush
  • Quickbase Alumni
  • December 28, 2021
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
------------------------------
Terms & ConditionsAccessibility statement