Formula to identify with min/max date if column is less than 1 and date is less than today?

Eddie Endurium LLC

how do I do this?

I have the below which grabs the count correctly for anything less than 100% and <= today's date, but I want to grab the min and max date.

=COUNTIFS({Estimating Percentage}, <1, {Est Start Date}, <=TODAY())

Mike TV

@Eddie Endurium LLC

I think I understand what you want but it took me a second. Your formula counts the # of percents that are less than 100% and have a date that's less than or equal to today's date. Then say you obtain a count of 50 from that. Of those 50, you'd like to know what the minimum date and maximum date are. Is that correct?

Eddie Endurium LLC

Correct, thanks...

Mike TV

@Eddie Endurium LLC

Ok. I'm not sure that I can think of a formula right now for that. However, I think it would be easy to create a report to do this. First, create a report that has a filter of <100% for the percent column. Another filter for the date less than today. Then sort it by the date column. Then just look at the top date and the bottom date.

Will that work for you?

Eddie Endurium LLC

I'm looking for the formula that could produce the cell value in my metrics sheet that I could reference and display on my dashboard.

I know this pulls the count: =COUNTIFS({Estimating Percentage}, <1, {Est Start Date}, <=TODAY())

I presumed that would be relevant to look through 200 rows to narrow down on the criterion and pull the date.

I may be wrong.

Mike TV

@Eddie Endurium LLC

You'll need to set the filters like this: