Help

On or after (or before) (rather than IS_AFTER)

Topic Labels: Formulas
2104 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Harrison_Jones
5 - Automation Enthusiast
5 - Automation Enthusiast

Hey All,

I have a formula below that checks if a rental is currently Active.

I would like it to include bookings that are finishing today also as an “Active Rental :blue_car: ” ( and also bookings that start today). Is there a way to do that (or a better way to construct this formula?

My formula is below;

IF( IS_AFTER( {Date Started}, TODAY() ), “Upcoming :calendar: ”, IF( IS_AFTER( {Date Ending}, TODAY() ), “Active Rental :blue_car: ”, “Completed :white_check_mark: ” ) )

Thanks for any help.

Cheers,
Harry

2 Replies 2

Hi @Harrison_Jones - try this:

IF(
  AND({Date Started}, {Date Ending}, {Date Started} <= {Date Ending}),
  IF(
    AND({Date Started} <= TODAY(), {Date Ending} >= TODAY()),
    'Active', 
    IF(
      {Date Started} > TODAY(),
      'Upcoming', 
      'Completed')
  ),
  'Error'
) 

Explanation:

This part:

AND({Date Started}, {Date Ending}, {Date Started} <= {Date Ending}),

Is checking that there is a start date, an end date and the start dates is less than or equal to the end date. If any of these are not true it throws an error and does not do any of the active, upcoming etc, evaluation.

The rest is the evaluation itself:

  IF(
    AND({Date Started} <= TODAY(), {Date Ending} >= TODAY()),
    'Active', 
    IF(
      {Date Started} > TODAY(),
      'Upcoming', 
      'Completed')
  ),

Screenshot 2019-07-30 at 01.36.50.png

JB

Harrison_Jones
5 - Automation Enthusiast
5 - Automation Enthusiast

JB

You legend!

Thank you so much.

Harry