Return date of 1st Mon in month which is not a Bank Holiday/weekend

[halesowenmum]

Hi all

In cell J4 I have the date 01/11/2018.
In cell B15 I want to return the date of the first Monday in the month of November (calculating off the date in J4 (which will always be the first of the month))
BUT
where the Monday is a Bank Holiday return the date for the Tuesday, if the bank holiday were a Friday return the date of the following Monday so long as it's not a bank holiday, etc - so Mondays but so long as they're not bank holidays or a weekend day).

The list of bank holidays is in a separate worksheet entitled 'Lists' and the named range of the BHs is 'Bank_holidays'.

Is it possible to do this?

Many thanks indeed.

 

[AlanY]

this will work out the first Monday of the date in J4

=DATE(YEAR(J4),MONTH(J4),DAY(DATE(YEAR(J4),MONTH(J4),8)-WEEKDAY(DATE(YEAR(J4),MONTH(J4),6))))

you can check this against the BHs list, if it's Monday then add 1 day

 

[halesowenmum]

Thank you - however, does this mean I've got to manually check something and then manually add something???

 

[AlanY]

let say your dates in Col A, Col B workd out the first Monday, Col C check these against the BH_list, and add 1 day if it's a Bank holiday Monday.
you can combine Cols B&C into 1 if wanted

Book1
	A	B	C	D	E
1	01/09/2018	03/09/2018	03/09/2018		BH
2	01/10/2018	01/10/2018	01/10/2018		15/07/2018
3	01/11/2018	05/11/2018	06/11/2018		08/08/2018
4	01/12/2018	03/12/2018	03/12/2018		04/09/2018
5					28/09/2018
6					19/10/2018
7					05/11/2018
Sheet1

Cell Formulas
Range	Formula
B1	=DATE(YEAR(A1),MONTH(A1),DAY(DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))))
C1	=IF(ISERROR(MATCH(B1,E:E,0)),B1,B1+1)

 

[halesowenmum]

Hi. Thanks very much indeed. I guess I'm not understanding why it can't be done (if this is the case) in one step, in one formula - all I want to do is in a filled down column of dates, for the first date (in cell B15) to be the first Monday of the month (or next non-weekend/non-holiday day) of the month. The bank holidays are already specifically defined in a named range so I thought I'd be able to refer to WORKDAYS (say) and the named range but tell it to find the next suitable working day...

Do I need to go ahead and create another named range for the weekends to enable it to be done in one fell swoop in a single formula???

I guess the add 1 day if it's a Monday is ok but what if it's Good Friday - adding one day wouldn't work and if it was a Bank Holiday over Xmas that fell on say a Wednesday, the add one day wouldn't work possibly?

 

[AlanY]

it's now combined to 1 formula, just difficult to read/audit, that's all

Book1
	A	B	C	D	E
1	01/09/2018		03/09/2018		BH
2	01/10/2018		01/10/2018		15/07/2018
3	01/11/2018		06/11/2018		08/08/2018
4	01/12/2018		03/12/2018		04/09/2018
5					28/09/2018
6					19/10/2018
7					05/11/2018
Sheet1

Cell Formulas
Range	Formula
C1	=IF(ISERROR(MATCH(DATE(YEAR(A1),MONTH(A1),DAY(DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6)))),E:E,0)),DATE(YEAR(A1),MONTH(A1),DAY(DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6)))),DATE(YEAR(A1),MONTH(A1),DAY(DATE(YEAR(A1),MONTH(A1),8)-WEEKDAY(DATE(YEAR(A1),MONTH(A1),6))))+1)

I guess the add 1 day if it's a Monday is ok but what if it's Good Friday - adding one day wouldn't work and if it was a Bank Holiday over Xmas that fell on say a Wednesday, the add one day wouldn't work possibly?

not quite understand, aren't you just wanted the first Monday of the month?

 

[steve the fish]

Im not quite sure what Fridays have got to do with finding the next Monday but try this and see if it gets the right day:

=WORKDAY(A1+6-WEEKDAY(A1-2),1,holidays)

 

[AlanY]

Im not quite sure what Fridays have got to do with finding the next Monday but try this and see if it gets the right day:

=WORKDAY(A1+6-WEEKDAY(A1-2),1,holidays)

mine is longer than yours