Checkbox formula with multiple dates to refrence

Question

Hello,&nbsp;I need to have three date fields Sunday dateDeparture Date&nbsp;Arrival DateI 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 DepartureAny thoughts on the formula?&nbsp;Thanks,Tara------------------------------Tara Stroup------------------------------

jeffpeterson1 · Answer

Hi Tara,Make a formula checkbox field, and assuming these are all 'Date' fields,&nbsp; this formula:[SundayDate] &lt;= [Departure] and [SundayDate] &gt;= [Arrival]or IsNull([Arrival]) or IsNull([Departure])------------------------------Jeff Peterson------------------------------

miketamoush · Answer

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

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

Jeff,I understood her ask differently. We interpret this scenario differently.Sunday Date &gt; 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------------------------------

jimharrison · Answer

&nbsp;I added the &lt;= and now that doesn't cause an error.

Forum Discussion

Checkbox formula with multiple dates to refrence