# Bank holiday to be factored in End of Month formula EOMONTH



## mrsushi (Dec 29, 2022)

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 (Dec 29, 2022)

Check information on Workday and /or Workday.intl  


Workday.xlsmEFG2Thu 29-Dec-22Thu 29-Dec-22 ACTION 3Thu 29-Dec-22ACTION1bCell FormulasRangeFormulaE2:E3E2=TODAY()F2F2=WORKDAY(EOMONTH(E2,0),-2,Holidays)G2G2=IF(TODAY()=E2, "ACTION", "DON’T ACTION")F3F3=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")Named RangesNameRefers ToCellsHolidays='1c'!$L$2:$L$3F2


----------



## mrsushi (Dec 29, 2022)

Dave Patton said:


> Check information on Workday and /or Workday.intl
> 
> 
> Workday.xlsmEFG2Thu 29-Dec-22Thu 29-Dec-22 ACTION 3Thu 29-Dec-22ACTION1bCell FormulasRangeFormulaE2:E3E2=TODAY()F2F2=WORKDAY(EOMONTH(E2,0),-2,Holidays)G2G2=IF(TODAY()=E2, "ACTION", "DON’T ACTION")F3F3=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")Named RangesNameRefers ToCellsHolidays='1c'!$L$2:$L$3F2



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 (Dec 29, 2022)

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

Workday.xlsmEFG3Thu 29-Dec-22ACTION4Fri 30-Dec-22DON’T ACTION51bCell FormulasRangeFormulaE3E3=TODAY()F3F3=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")F4F4=IF(E4=WORKDAY(EOMONTH(E4,0),-2,Holidays), "ACTION", "DON’T ACTION")Named RangesNameRefers ToCellsHolidays='1c'!$L$2:$L$3F4


----------



## mrsushi (Dec 30, 2022)

Dave Patton said:


> Did you try the formula with Dec 30th?
> It seems to work OK!
> 
> Workday.xlsmEFG3Thu 29-Dec-22ACTION4Fri 30-Dec-22DON’T ACTION51bCell FormulasRangeFormulaE3E3=TODAY()F3F3=IF(TODAY()=WORKDAY(EOMONTH(E3,0),-2,Holidays), "ACTION", "DON’T ACTION")F4F4=IF(E4=WORKDAY(EOMONTH(E4,0),-2,Holidays), "ACTION", "DON’T ACTION")Named RangesNameRefers ToCellsHolidays='1c'!$L$2:$L$3F4



Opps, seems to be working now.

Many thanks for your help on this.

Regards
M


----------



## mrsushi (Dec 30, 2022)

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 (Dec 30, 2022)

I got it to work with the below 

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


----------

