WORKDAY

[IvanYerk]

Boy I can tie my mind up in knots. I appreciate when this extremely awesome group putting good karma out into the universe can help untie it.

I want to return the 1st of the month, BUT if it falls on weekend or holiday, I want it to find the last working day prior. For example - in April 2023, 4/1 was on a saturday. I want it to find the first prior working day that isn't a weekend or holiday. So it returns March 31st.

Where I'm stuck is January 2024 when the 1st is on a Monday AND it's a holiday. So I need the formula to return December 29th. Or even if January 1st was a Wednesday, how do I get it to return 12/31?

I was using If/Then Weekday 7 or 1, go back 1 day or 2 days, respectively. But I'm not sure how to add the workday/holiday part to the formula.

Thanks so much as always!

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1
2	11/04/2023	31/03/2023		01/01/2024
3	13/01/2024	29/12/2023
4
5
Main

Cell Formulas
Range	Formula
B2:B3	B2	=WORKDAY(EOMONTH(A2,-1)+2,-1,$D$2:$D$10)

 

[IvanYerk]

Thanks Fluff, looks like it's working like a charm. Many thanks! It was the +2 that did the trick. Love it!

 

[Fluff]

You're welcome & thanks for the feedback.