get end of month date (business day)

[daveyc18]

i already have a holidays table set up

so today is 10/31/2020, but the end of month date for business purposes is 10/30/2020. the way my formula is structured right now is that it only picks up the EOM date correctly if the current date is in the following month because the next business day is the following month. (in this case, november)

drawing a blank here...is there a way to make it pick up the business end of month date regardless if it's, in this case, october or november...ie today is oct 31, so pick up oct 30...or when november rolls around, still pick up oct 30

 

[Dave Patton]

How do we know if you want the last Workday for the current or previous month?

Date and Time.xlsm
	A	B	C
1	5-Nov-20	30-Oct-20	Previous month
2	31-Oct-20	30-Oct-20	Current month
3	5-Oct-20	30-Oct-20	Current month
4	5-Nov-20	30-Nov-20	Current month
5
16a

Cell Formulas
Range	Formula
B1	B1	=WORKDAY(EOMONTH(A1,-1)+1,-1,Holidays)
B2:B4	B2	=WORKDAY(EOMONTH(A2,0)+1,-1,Holidays)

 

[daveyc18]

helpful as always

to answer your question ...because today (Oct 31 is a Saturday) ...it's technically "November "(next month) for us

typically it's not an issue as we would be usually working Nov 2 (Monday) to run our reports but it's quarter end so I had to log in today and ran into this issue

 

[Dave Patton]

Is your challenge resolved?

Date and Time.xlsm
	A	B	C
5
6	Fri 31-07-20	30-Jul-20	if you require Jul 30, could you add 1 to a special holidaylist?
7	Mon 03-08-20	30-Jul-20
16a

Cell Formulas
Range	Formula
B6	B6	=WORKDAY(EOMONTH(A6,0)+1,-1,Holidays)
B7	B7	=WORKDAY(EOMONTH(A7,-1)+1,-1,Holidays)

 

[daveyc18]

no..but I'm thinking of devising a formula an IF formula where if if it's the current month, use one formula, if it's the following month , then use another formula