Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=max function on dates
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=max function on dates
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. -- Biff Microsoft Excel MVP "VickiMc" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
=max function on dates
Hi,
Try something like: =SUMPRODUCT(--MAX((A2:A100)*(A2:A100<TODAY()))) Change the ref's to suit your data. Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If Function with Dates | Excel Worksheet Functions | |||
Using IF Function on Dates | Excel Discussion (Misc queries) | |||
If Function and Dates | Excel Worksheet Functions | |||
If Between Dates Function - please help | Excel Worksheet Functions | |||
IF function and DATES | Excel Worksheet Functions |