get end of month date (business day)

daveyc18

Active Member
Joined
Feb 11, 2013
Messages
473
Office Version
  1. 365
  2. 2010
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
 

Some videos you may like

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
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 11, 2013
Messages
473
Office Version
  1. 365
  2. 2010
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
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Feb 11, 2013
Messages
473
Office Version
  1. 365
  2. 2010
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
 

Watch MrExcel Video

Forum statistics

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

Which adblocker are you using?

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
Top