=max function on dates

[VickiMc]

I was hoping for assistance on a formula to calculate the maximum date in a
range that is less than or equal to today()?
My ultimate aim is to then deduct this date from =today().
What I'm working with is a spreadsheet of Employees Leave Breaks and I need
to show the boss the number of days each employee has been back on site since
their last break.
=Max alone doesn't quite cut it because the range includes a list of
forecast break dates.
I also tried Index Match but couldn't manage to get it to work either.

 

[T. Valko]

Try this array formula** :

=MAX(IF(A1:A10<=TODAY(),A1:A10))

My ultimate aim is to then deduct this date from =today()

=TODAY()-MAX(IF(A1:A10<=TODAY(),A1:A10))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Dave]

Hi,
Try something like:

=SUMPRODUCT(--MAX((A2:A100)*(A2:A100<TODAY())))

Change the ref's to suit your data.

Regards - Dave.