last date until today in range

[Lukas C]

Hi i have a wee array formula here to show me last booking
{=MAX(IF('[calendar.xlsx]full year'!$D$8:$ABG$103=B2,'[calendar.xlsx]full year'!$D$4:$ABG$4,""))}
'[calendar.xlsx]full year'!$D$8:$ABG$103 - is where you book names
B2 - client name to look for
'[calendar.xlsx]full year'!$D$4:$ABG$4 - range of dates from 1/1 to 31/12

but how can i adjust it to show me last booking until today and first booking after today ?

any help much appreciated

 

[James006]

Hi,

As a blind test ...following array formula :

=MAX(IF('[calendar.xlsx]full year'!$D$8:$ABG$103=B2,IF('[calendar.xlsx]full year'!$D$4:$ABG$4 <=TODAY(),'[calendar.xlsx]full year'!$D$4:$ABG$4),""))

Hope this will help

 

[Lukas C]

yes that should work mega thx, sometimes u so deep in the forest u cant see the woods

 

[James006]

You are welcome ...