LAST BUSINESS DAY OF MONTH?

[daveyc18]

=WORKDAY(EOMONTH(report_date,-1),-1, holidays) YIELDS .. "APRIL 28, 2023" because the report_date is 5/1/2023

but if i apply that same formula for 2 month ago

=WORKDAY(EOMONTH(report_date,-2),-1, holidays) , im getting "March 30,2023," but I want March 31, 2023

what am i doing wrong here?


if i change it to "=WORKDAY(EOMONTH(report_date,-2),0, holidays)", then I get "March 31, 2023" , but obviously changing the formula depending on the month is not the ideal solution

 

[awoohaw]

you are using the EOMONTH function so you're starting date for workday.intl is 3/31. You need to Add 1 to it to get the work day prior.

 

[awoohaw]

here it is in action:

Book4
	A	B	C	D	E	F	G	H
1	2023-02-01	2023-03-01	2023-04-01	2023-05-01	2023-06-01	2023-07-01	2023-08-01	2023-09-01
2	3	3	6	1	4	6	2	5
3	Wednesday	Wednesday	Saturday	Monday	Thursday	Saturday	Tuesday	Friday
4
5	2023-01-31	2023-02-28	2023-03-31	2023-04-28	2023-05-31	2023-06-30	2023-07-31	2023-08-31
6	2	2	5	5	3	5	1	4
7	Tuesday	Tuesday	Friday	Friday	Wednesday	Friday	Monday	Thursday
8
9	Report Day:	2023-05-01
10	Last BD 1 Mnth	2023-04-28
11	Last BD 2 Mnth	2023-03-31
Sheet9

Cell Formulas
Range	Formula
B1:H1	B1	=EOMONTH(A1,0)+1
A2:H2,A6:H6	A2	=WEEKDAY(A1,2)
A3:H3,A7:H7	A3	=A1
A5:H5	A5	=WORKDAY.INTL(A1,-1,1)
B10	B10	=WORKDAY.INTL($B9,-1,1)
B11	B11	=WORKDAY.INTL(EOMONTH(B9,-2)+1,-1,1)

 

[daveyc18]

you are using the EOMONTH function so you're starting date for workday.intl is 3/31. You need to Add 1 to it to get the work day prior.

sorry not on my PC rn but would that not mess up my first formula that correctly picks up April 28?

 

[awoohaw]

sorry not on my PC rn but would that not mess up my first formula that correctly picks up April 28?

not at all... because you have two different formulas already.

But, I'll post single formula in a minute.

 

[awoohaw]

Here is formula that, I think, yields what you want:

Book4
	A	B	C	D
1	Report Day:	2023-05-01
2
3		Desired Report EO Month	How many Months ago
4	Last BD 1 Mnth	April	1	2023-04-28
5	Last BD 2 Mnth	March	2	2023-03-31
Sheet9

Cell Formulas
Range	Formula
D4:D5	D4	=WORKDAY.INTL(EOMONTH($B$1,-$C4)+1,-1,1)

 

[Fluff]

would that not mess up my first formula that correctly picks up April 28?

No because 30th Apr is a Sunday, whereas 31st Mar is a Friday. Your formula will only work correctly if the last day of the month is a Saturday or Sunday.
As has been said, you need to add one to the end of the month so that your date is the 1st of the next month.

=WORKDAY(EOMONTH(report_date,-2)+1,-1, holidays)