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

Options

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())

Tags:

• ✭✭✭✭✭✭
Options

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?

• Options

Correct, thanks...

• ✭✭✭✭✭✭
Options

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?

• Options

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.

• ✭✭✭✭✭✭
Options

You'll need to set the filters like this:

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!