Status Formula Help

Topic Labels: Formulas
1086 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hi all,

I’ve got a request about a specific formula. I’m creating a rental database and have ‘Start Date’ and ‘End Date’ columns that link to calendar events (jobs).

I’m looking for a formula that I can put in a ‘Status’ column. Is there a way to have an IF statement that returns “Booked” if the start date is before today, “In Use” if today is between the start and end dates, and “Available” if today is after the end date.

I just can’t quite wrap my head around it since most date formulas return either a 1, or 0 and I’m working with 3 return values.

Right now I’ve got the following: IF(IS_AFTER({Start Date}, TODAY()), “Booked”, IF(IS_AFTER(TODAY()), {End Date}), “Available”, “In Use”)) but it is not working.

any help is appreciated, thanks!

1 Reply 1
  1. IS_AFTER([date1], [date2]), as documentation says, determines if [date1] is later than [date2]. Returns 1 if yes, 0 if no. I think you must change the order of the dates inside IS_AFTER.
  2. Also, {End Date} is outside the IS_AFTER function.