The MIN and MAX functions are useful as for returning the first and last dates in a range. They have conditional versions as well MINIFS and MAXIFS which allow you to find the earliest and latest dates based on a condition.
They work because Excel stores dates as numbers.
In the image below we have sales in July (Australian dates) for our three products.
We can use the conditional functions to find the first and last sale date for each product.
The syntax of both functions is the same as SUMIFS.
The range to perform the calculation on is the first argument. The second argument is the first condition range, and the third argument is the first condition to look for.
Both functions can handle more than one condition.
This provides a straightforward calculation to determine first and last dates based on a condition or multiple conditions.
Here is another formula that would work…
=TAKE(FILTER(A$2:A$11,B$2:B$11=E2),{1,-1})
I forgot to mention… my formula spills both the minimum and maximum dates from the single formula.
Thanks Rick – this assumes the dates are sorted. If they aren’t sorted you would need to add a SORT around the FILTER.
Here is one more formula that will spill the minimum and maximum dates across the row and, unlike my previous formula, this one does not require sorted data…
=BYCOL(FILTER(A$2:A$11,B$2:B$11=E2),HSTACK(MIN,MAX))
Thanks Rick – cool – using the two functions together – still getting used to using these without brackets.