The “Replaced” column shows the date equipment was replaced.
The “Expires” column shows the date it will need to be replaced again.
Datesalready expiredare highlighted in yellow with conditional formatting.
The named ranges are for convenience only, to make the formula easier to read and write.
you’re able to use this same approach to filter on any data that has an upcoming date.
retirements, events, renewals, etc.
Note: In the example shown, the current date is 7-July-2022, provided by the TODAY function.
When the expiration date is in the past, the result is anegativenumber.
When the expiration date is in the future, the result date is apositivenumber.
In the example shown:
returns the followingarray:
Each number represents the number of days until expiration.
Again, negative numbers are datesalready expired.
We can use this same logic in the FILTER function to select dates expiring soon.
FILTER function
The next step is to implement the logic above inside theFILTER function.
Note that this result is based on the current date in cell H2, which is 7-July-2022.
This is often what you want in real life, where the data being tracked is always changing.
Conditional formatting
In the example shown,conditional formattingis used to highlight dates that havealreadyexpired.
This is an example of amixed reference the column is locked in order tohighlight the entire row.
Expires date
The date in the “Expires” column is calculated with theEDATE function.
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
Values can be sorted by one or more columns.
SORT returns a dynamic array of results.
AND returns TRUEonly if all the conditions are met.
If any conditions are not met, the AND function returns FALSE.
The TODAY function takes no arguments.
you could format the value returned by TODAY with a datenumber format.
If you need current date and time, use…
