Bank holiday to be factored in End of Month formula EOMONTH

[mrsushi]

Hi, how can the below formula factor in bank holidays? So the below formula works off E2 which is todays date ie 29th Dec.

Ideally, the below formula is to trigger an ACTION 2 working days before the end of the month. However, the 31st Dec is a Saturday, so we need to factor this in. how can we incorporate a list of bank holiday dates using the below formula?

=IF(EOMONTH(TODAY(), 0)=E2, "ACTION", "DON’T ACTION")

Many thanks
M

 

[Dave Patton]

Check information on Workday and /or Workday.intl

Workday.xlsm
	E	F	G
2	Thu 29-Dec-22	Thu 29-Dec-22	ACTION
3	Thu 29-Dec-22	ACTION
1b

Cell Formulas
Range	Formula
E2:E3	E2	=TODAY()
F2	F2	=WORKDAY(EOMONTH(E2,0),-2,Holidays)
G2	G2	=IF(TODAY()=E2, "ACTION", "DON’T ACTION")
F3	F3	=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")

Named Ranges
Name	Refers To	Cells
Holidays	='1c'!$L$2:$L$3	F2

 

[mrsushi]

Check information on Workday and /or Workday.intl

Workday.xlsm
	E	F	G
2	Thu 29-Dec-22	Thu 29-Dec-22	ACTION
3	Thu 29-Dec-22	ACTION
1b

Cell Formulas
Range	Formula
E2:E3	E2	=TODAY()
F2	F2	=WORKDAY(EOMONTH(E2,0),-2,Holidays)
G2	G2	=IF(TODAY()=E2, "ACTION", "DON’T ACTION")
F3	F3	=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")

Named Ranges
Name	Refers To	Cells
Holidays	='1c'!$L$2:$L$3	F2

Many thanks Dave. However, if the date is 30/12/2022, it needs to return No Action. The current formula appears to stay Action when it is 30th Dec, but we need to be as per below

29th represents 2 working days left = Action
whereas 30th represents 1 working day left = No action

 

[Dave Patton]

Did you try the formula with Dec 30th?
It seems to work OK!

Workday.xlsm
	E	F	G
3	Thu 29-Dec-22	ACTION
4	Fri 30-Dec-22	DON’T ACTION
5
1b

Cell Formulas
Range	Formula
E3	E3	=TODAY()
F3	F3	=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")
F4	F4	=IF(E4=WORKDAY(EOMONTH(E4,0),-2,Holidays), "ACTION", "DON’T ACTION")

Named Ranges
Name	Refers To	Cells
Holidays	='1c'!$L$2:$L$3	F4

 

[mrsushi]

Did you try the formula with Dec 30th?
It seems to work OK!

Workday.xlsm
	E	F	G
3	Thu 29-Dec-22	ACTION
4	Fri 30-Dec-22	DON’T ACTION
5
1b

Cell Formulas
Range	Formula
E3	E3	=TODAY()
F3	F3	=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")
F4	F4	=IF(E4=WORKDAY(EOMONTH(E4,0),-2,Holidays), "ACTION", "DON’T ACTION")

Named Ranges
Name	Refers To	Cells
Holidays	='1c'!$L$2:$L$3	F4

Opps, seems to be working now.

Many thanks for your help on this.

Regards
M

 

[mrsushi]

Just one last question, how would the formula change if we need the conditions?

"Action" on first working day of month ie 03/01/2023
"Action" on second working day of month 04/01/2023

I tried to tweak the formula, but doesnt seem to give me the returns

IF(TODAY()=WORKDAY(EOMONTH(E2,0),-1,Holidays), "Action", "No Action")

 

[mrsushi]

I got it to work with the below

=IF(E2=WORKDAY(DATE(YEAR(E2),MONTH(E2),1)-1,1,Holidays!A:A), "LOAD", "SCOPE")