Excel Formula or VBA Last month last Business day

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
466
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
Hello - Looking for a way to pull last month's last business day based off of today's date, formula or VBA will work. I tried using EOMONTH but it just returns me current month last day. See below:

Book2
AB
1Today9/2/2020
2Last BD of Last Month8/31/2020
Sheet4
Cell Formulas
RangeFormula
B1B1=TODAY()
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
686
Office Version
  1. 365
Platform
  1. Windows
I just tried
Code:
=EOMONTH(TODAY(),-1)
and it gave me 8/31/2020. Isn't that what you want?
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
466
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows
I just tried
Code:
=EOMONTH(TODAY(),-1)
and it gave me 8/31/2020. Isn't that what you want?
But does that just give last day and not last business day? that is the other dilemma i was having
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
How about
=WORKDAY(EOMONTH(TODAY(),-1)+1,-1)
 

ItalianPlatinum

Active Member
Joined
Mar 23, 2017
Messages
466
Office Version
  1. 2016
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

How about
=WORKDAY(EOMONTH(TODAY(),-1)+1,-1)
that is returning last day. i tried 6/2 as a test and it's yielding 5/31 should be 5/29. but i think i can work around it i can use last day still and just apply filtering to get what i need
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
66,064
Office Version
  1. 365
Platform
  1. Windows
Not for me it doesn't, did you change the formula?
+Fluff New.xlsm
B
102/06/2020
229/05/2020
Data
Cell Formulas
RangeFormula
B2B2=WORKDAY(EOMONTH(B1,-1)+1,-1)
 

Forum statistics

Threads
1,148,391
Messages
5,746,435
Members
424,017
Latest member
jaka

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