Forum Discussion

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 depar...

Formulas and functions

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
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
  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
  ------------------------------