Status Formula Help

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

1 Like