=max function on dates

  • Thread starter Thread starter VickiMc
  • Start date Start date
V

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.
 
Try this array formula** :

=MAX(IF(A1:A10<=TODAY(),A1:A10))
My ultimate aim is to then deduct this date from =today()
Click to expand...

=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.
 
Hi,
Try something like:

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

Change the ref's to suit your data.

Regards - Dave.
 
Back
Top