RYG formula based on start/end date columns

mmac

Hello,

I'm looking to accomplish the following and am hopeful that someone will be able to help support. Thank you in advance.

I have a Live, Start Date and End Date column.

What I am proposing to accomplish:

Live = Red, if the Start Date/End Date are in the past (*Sale has expired)

Live = Green if Start Date is today's date and the End Date is in the future (Sale is active, but has not yet expired)

Hopefully they above is fairly straight forward and I was able to make sense of it. This will act as a visual to see what active sales are going on and what sales have come to an end.

Paul Newcome

Try something like this...

=IF(AND([Start Date]@row < TODAY(), [End Date]@row < TODAY()), "Red", IF(AND([Start Date]@row = TODAY(), [End Date]@row > TODAY()), "Green"))

mmac

@Paula Cosentino Thanks so much. I think that solves for it. The other question I see though, if for existing entries in the sheet. I applied the formula existing entry for a sale that based on start/end would technically be "live" and the Green ball is not appearing. Are further adjustments needing to be made?

This works like a charm for net new entries of sales that are in the future though.

Paul Newcome

@mmac My apologies. I was providing a formula that fit your post literally. I am assuming now that you would like the Green ball to be displayed if the [End Date] is in the future and the [Start Date] is not just equal to today, but also in the past. This should do the trick for that.

=IF(AND([Start Date]@row < TODAY(), [End Date]@row < TODAY()), "Red", IF(AND([Start Date]@row <= TODAY(), [End Date]@row > TODAY()), "Green"))

mmac

@Paul Newcome Appreciate the prompt reply. That covers that scenario! The other one that has crept up I am now realizing, which is in addition to the above, is that if a sale is "on-going" and has no end date. The idea would be that if start date is in the past and end date is blank, the Live column would display a green ball. Because technically, the sale would still be live.

Paul Newcome

Ok. So for green we would have the already established

[Start Date]@row <= TODAY()

AND

[End Date]@row > TODAY() or ISBLANK([End Date]@row)

So lets work backwards and start with the OR

OR([End Date]@row > TODAY(), ISBLANK([End Date]@row))

Now we can drop that into the AND statement alongside the already established criteria for [Start Date]@row

AND([Start Date]@row <= TODAY(), OR([End Date]@row > TODAY(), ISBLANK([End Date]@row)))

Then drop that in place for the logical statement portion that generates a "Green".

=IF(AND([Start Date]@row < TODAY(), [End Date]@row < TODAY()), "Red", IF(AND([Start Date]@row <= TODAY(), OR([End Date]@row > TODAY(), ISBLANK([End Date]@row))), "Green"))