- MS Excel Basics
- Excel - Home
- Excel - Getting Started
- Excel - Explore Window
- Excel - Backstage
- Excel - Entering Values
- Excel - Move Around
- Excel - Save Workbook
- Excel - Create Worksheet
- Excel - Copy Worksheet
- Excel - Hiding Worksheet
- Excel - Delete Worksheet
- Excel - Close Workbook
- Excel - Open Workbook
- Excel - Context Help
- Editing Worksheet
- Excel - Insert Data
- Excel - Select Data
- Excel - Delete Data
- Excel - Move Data
- Excel - Rows & Columns
- Excel - Copy & Paste
- Excel - Find & Replace
- Excel - Spell Check
- Excel - Zoom In-Out
- Excel - Special Symbols
- Excel - Insert Comments
- Excel - Add Text Box
- Excel - Undo Changes
- Formatting Cells
- Excel - Setting Cell Type
- Excel - Setting Fonts
- Excel - Text Decoration
- Excel - Rotate Cells
- Excel - Setting Colors
- Excel - Text Alignments
- Excel - Merge & Wrap
- Excel - Borders and Shades
- Excel - Apply Formatting
- Formatting Worksheets
- Excel - Sheet Options
- Excel - Adjust Margins
- Excel - Page Orientation
- Excel - Header and Footer
- Excel - Insert Page Breaks
- Excel - Set Background
- Excel - Freeze Panes
- Excel - Conditional Format
- Working with Formula
- Excel - Creating Formulas
- Excel - Copying Formulas
- Excel - Formula Reference
- Excel - Using Functions
- Excel - Builtin Functions
- Advanced Operations
- Excel - Data Filtering
- Excel - Data Sorting
- Excel - Using Ranges
- Excel - Data Validation
- Excel - Using Styles
- Excel - Using Themes
- Excel - Using Templates
- Excel - Using Macros
- Excel - Adding Graphics
- Excel - Cross Referencing
- Excel - Printing Worksheets
- Excel - Email Workbooks
- Excel- Translate Worksheet
- Excel - Workbook Security
- Excel - Data Tables
- Excel - Pivot Tables
- Excel - Simple Charts
- Excel - Pivot Charts
- Excel - Keyboard Shortcuts
- MS Excel Resources
- Excel - Quick Guide
- Excel - Useful Resources
- Excel - Discussion
How to get the last business day of previous month based on today in Excel?
In this article, the user will understand the concept of generating the last business day of the previous month in Excel. The first example will guide the user through the process of using the formula, while the second example performs the same task by using the VBA code. For example, if today is May 6, 2023, then the date April 29, 2023, would be the last working day of the preceding month, assuming that weekends and holidays are not considered business days.
Example 1: By using the user-defined formula:
To understand the process of using the example. Consider the below-provided worksheet. Please note that the provided Excel sheet only contains a column header in the E2 cell, along with a space for E3.
Go to the E3 cell and type formula “=WORKDAY(DATE(YEAR(TODAY()),MONTH(TODAY()),1),-1)”.
Explanation for the above formula:
TODAY() method will return the current date in the Excel date format.
YEAR(TODAY()) method will return the year component of the current date.
MONTH(TODAY()) method will return the month according to the current date.
DATE(YEAR(TODAY()), MONTH(TODAY()),1) method will create a new date by combining the year and month of the current date and setting the day to the first day of the month. For example, if the current date is May 6, 2023, this function will return on May 1, 2023.
-1 is subtracted from the date returned by the DATE function, which moves the date back one day.
WORKDAY() method will calculate the last working day before the date obtained from the above step. The WORKDAY function takes into account any holidays or weekends, which are specified by optional arguments.
The above-provided formula will display the below-generated result. To convert the generated number to a valid date, the user needs to change the respective formula from number to date type.
Go to the “Number” option displayed below, click on the drop-down arrow, and select the option “Date”. Consider the below-depicted image for proper reference −
The above change will convert the number to date data type. Consider the below-provided image for reference −
Example 2: By using the VBA code:
In this example, we will take the same Excel sheet, as chosen for the above example,
In this example, the user will understand the process of generating and executing the VBA code, and to execute the VBA code user need to open the VBA code editor. To do so, go to the “Developer” tab, and under the “Code” section, choose the option for “Visual Basic”. Consider the snapshot provided below −
The above step will open a “Microsoft Visual Basic for Applications” dialog box, as highlighted below −
In the appeared dialog box, click on the “Insert” tab, and select the option for “Module”.
The above step will open an empty code editor. In the opened blank area, type the below-given code −
' define function header Function LastBusinessDayOfPrevMonth() As Date ' declaring required variables Dim lDy As Integer Dim prMnth As Integer Dim p_year As Integer Dim lastBusinessDay As Date ' calling day() method, with required data values lDy = Day(DateSerial(Year(Date), Month(Date), 0)) ' Get the last day of the current month prMnth = Month(Date) - 1 ' Get the previous month p_year = Year(Date) ' Get the year of the previous month ' if block to check value If prMnth = 0 Then ' If the previous month is December, adjust the year and month accordingly ' processing step for execution of if block prMnth = 12 p_year = Year(Date) - 1 ' end of if block End If ' calling last business day lst_BnsDay = DateSerial(p_year, prMnth, lDy) ' Combine the previous year, previous month and last day of the current month ' while loop While Weekday(lst_BnsDay, vbMonday) = 6 Or Weekday(lst_BnsDay, vbMonday) = 7 ' Check if the last day of previous month is a weekend day ' processing step lst_BnsDay = DateAdd("d", -1, lst_BnsDay) ' If the last day is a weekend day, move it back by one day until a weekday is reached Wend LastBusinessDayOfPrevMonth = lst_BnsDay ' Return the last business day of previous month ' end of function definition End Function
After that use key combination for “Alt +Q” to exit the sheet, and call the method “=LastBusinessDayOfPrevMonth()”, as depicted below −
Press the “Enter” key to display the required result in date format. Please found below attached final snapshot for output −
With the help of this article, users can obtain the last working/business day based on today's date. Two examples are demonstrated in this article. The user-defined formula is used in the first formula to retrieve the last business day of the previous month and in the second example, the program code is written in the VBA to generate the same result.
- Related Articles
- How to get last day of the previous month in MySQL?
- How to get the first day of the previous month in MySQL?
- How to get last day of the current month in MySQL?
- How to get last day of the next month in MySQL?
- Get the last day of month in Java
- How to get last day of a month in Python?
- How to get the same or first day of next month on a given date in Excel?
- Java Program to get day of week for the last day of each month in 2019
- How to get the previous day with MySQL CURDATE()?
- How to display first day and last day of the month from date records in MySQL?
- How to get subtotals based on invoice number in Excel?
- How to get the first day of next month in MySQL?
- Group by day/month/week based on the date range in MongoDB
- How to get the first day of the current month in MySQL?