Calculated Field: does date fall within start/exit date?

[cheena12]

I am trying to create a calculated field that will flag if a service date falls outside the enrollment range (start date and exit date) of a client. If an exit date is null, the date used should be “today”. I am NOT a developer so your patience and assistance will be greatly appreciated.

I tried using my Excel skills to see if they would translate but did not work:

=IF(AND(${services.start_date_date}=>${enrollments.start_date},${services.start_date_date}<=${enrollments.end_date}),"VALID","INVALID") 

[jcdufault · Accepted solution]

Hi!

Based on the dimensions you shared, I think something like this would work for your use case:

if(
if(is_null(${enrollments.start_date}),now(),${enrollments.start_date})<${services.start_date_date} 
OR if(is_null(${enrollments.end_date}),now(),${enrollments.end_date})>${services.start_date_date},
"outside","inside")

First we check if the enrolment date start is null, if it is, we return the current time, if it isn’t, we return the enrolment date. 

Then we compare that result with the service start date. We repeat the process after the OR but this time for the end date. 

Hope this helps! 

[cheena12]

Thank you, @jcdufault !