Thread: First Date and Time Following

Am so sorry, that was my fault completely- didnt realise i was calculating January with December formula!!

Ermm, instead of H$2:H$10 i was using rngDynHolidayList to calculate the holidays.
But when i replace it in the formula for December it doesnt work correctly. Please advise.


=INT(A28+B28-"16:30")+MIN(IF(ISNUMBER(MATCH(WEEKDAY(A28+B28-"16:30"+{1,2,3,4,5,6,7}),{3,5,6},0))+(MONTH(A28+B28-"16:30"+{1,2,3,4,5,6,7})=12)*(WEEKDAY(A28+B28-"16:30"+{1,2,3,4,5,6,7},2)<6),IF(ISNA(MATCH(INT(A28+B28-"16:30"+{1,2,3,4,5,6,7}),rngDynHolidayList,0)),{1,2,3,4,5,6,7})))

Sorry, I did reply to this thread yesterday but just as I posted I lost the connection and then I couldn't log back in.........

I tested again and can't see a problem as long as your holiday range is defined as a single column (or row). Can you give examples where it didn't work - did you get errors or the wrong results?

Note: that formula should work for any date. It will automatically adjust to give extra collection days in December but only Tue, Thu and Fri in other months.

Note: if you holiday range isn't a single row or column you can use this version to accommodate that

=INT(A28+B28-"16:30")+MIN(IF(ISNUMBER(MATCH(WEEKDAY(A28+B28-"16:30"+{1,2,3,4,5,6,7}),{3,5,6},0))+(MONTH(A28+B28-"16:30"+{1,2,3,4,5,6,7})=12)*(WEEKDAY(A28+B28-"16:30"+{1,2,3,4,5,6,7},2)<6),IF(COUNTIF(rngDynHolidayList,INT(A28+B28-"16:30"+{1,2,3,4,5,6,7}))=0,{1,2,3,4,5,6,7})))

Thanks daddylonglegs, I have got that working

30th Nov even though it was a Monday, we still had collections on that date. Is there a way I can include it in the formula:

=INT(A28+B28-"16:30")+MIN(IF(ISNUMBER(MATCH(WEEKDAY(A28+B28-"16:30"+{1,2,3,4,5,6,7}),{3,5,6},0))+(MONTH(A28+B28-"16:30"+{1,2,3,4,5,6,7})=12)*(WEEKDAY(A28+B28-"16:30"+{1,2,3,4,5,6,7},2)<6),IF(ISNA(MATCH(INT(A28+B28-"16:30"+{1,2,3,4,5,6,7}),rngDynHolidayList,0)),{1,2,3,4,5,6,7})))

Cheers