# get end of month date (business day)

#### daveyc18

##### Active Member
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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### Dave Patton

##### Well-known Member
How do we know if you want the last Workday for the current or previous month?

Date and Time.xlsm
ABC
15-Nov-2030-Oct-20Previous month
231-Oct-2030-Oct-20Current month
35-Oct-2030-Oct-20Current month
45-Nov-2030-Nov-20Current month
5
16a
Cell Formulas
RangeFormula
B1B1=WORKDAY(EOMONTH(A1,-1)+1,-1,Holidays)
B2:B4B2=WORKDAY(EOMONTH(A2,0)+1,-1,Holidays)

#### daveyc18

##### Active Member
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

##### Well-known Member
Is your challenge resolved?

Date and Time.xlsm
ABC
5
6Fri 31-07-2030-Jul-20if you require Jul 30, could you add 1 to a special holidaylist?
7Mon 03-08-2030-Jul-20
16a
Cell Formulas
RangeFormula
B6B6=WORKDAY(EOMONTH(A6,0)+1,-1,Holidays)
B7B7=WORKDAY(EOMONTH(A7,-1)+1,-1,Holidays)

#### daveyc18

##### Active Member
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

Replies
13
Views
219
Replies
2
Views
106
Replies
5
Views
72
Replies
5
Views
229
Replies
3
Views
166

Threads
1,128,074
Messages
5,628,484
Members
416,321
Latest member
tomazik123

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back