Md. Shamim Reza
This article illustrates how to find the last business day of a month in excel. It also shows how to find the last business day/workday in any year. The WORKDAY function will allow you to do that. There is an alternative formula too. The following picture highlights the purpose of this article. Have a quick look through the article to learn how to do it.
Download Practice Workbook
You can download the practice workbook from the download button below.
9 Ways to Find Last Business Day of a Month in Excel
Here I am going to illustrate various examples for you to find the last business day of a month in excel. So let’s begin.
1. Last Business Day of Current Month in Excel
You can use the following formula to find the last business day of the current month.
=WORKDAY(EOMONTH(TODAY(),0)+1,-1)
🔎 How Does the Formula Work?
➤ TODAY()
The TODAY function returns the date of the present day.
➤ EOMONTH(TODAY(),0)
The EOMONTH function returns the end date of the month before or after a specified number of months. Here it returns the end date of the current month because of argument 0.
➤ +1 changes the last day of the month to the first day of the next month. -1 makes the WORKDAY function to return the last business day before the first day of the next month.
Read More: How to Get First Day of Month from Month Name in Excel (3 Ways)
2. Last Business Day of Current Month from a Given Date
Assume you have some date values in the range B5:B16. Then enter the following formula in cell C5. Next, use the fill handle icon to apply the formula to the cells below. After that, you will see the following result.
=WORKDAY(EOMONTH(B5,0)+1,-1)
Related Content: How to Get Last Day of Previous Month in Excel (3 Methods)
3. Last Business Day of Current Month Excluding Holidays
You can apply the following formula in cell C5 to find the last business day of a month excluding holidays.
=WORKDAY(EOMONTH(B5,0)+1,-1,$F$5:$F$13)
Read More: Get the First Day of the Current Month in Excel (3 Methods)
4. Last Business Day of the Month in Excel (International)
Business days are different country-wise. For example, workdays in Saudi Arabia are from Sunday to Thursday. But the WORKDAY function considers Monday to Friday as business days by default. You can use WORKDAY.INTL function instead for custom workdays. Apply the following formula in cell C5 to get the last business day of the month for countries with such business days.
=WORKDAY.INTL(EOMONTH(B5,0)+1,-1,7)
Here 7 in the formula is to specify that Friday to Saturday is weekend days as opposed to the default Saturday to Sunday in the WORKDAY function.
Read More: How to Get the Last Day of Month Using VBA in Excel (3 Methods)
5. Last Business Day of the Month Excluding Holidays (International)
Here we will use the holidays in Bolivia. Besides, only Sundays are weekend days in this country. So, you need to enter the following formula in cell C5 to find the last business day of a month in that country.
=WORKDAY.INTL(EOMONTH(B5,0)+1,-1,11,$F$3:$F$16)
Related Content: Excel VBA: First Day of Month (3 Methods)
Similar Readings:
- How to Convert Date to Month and Year in Excel (4 Ways)
- Change Date Format in Pivot Table in Excel
- Remove Time from Date in Pivot Table in Excel (A Step by Step Analysis)
- VBA to Remove Time from Date in Excel (3 Methods)
- How to Convert Text to Date with Excel VBA (5 Ways)
6. Last Weekday of the Current Month in Excel
You can also use the WEEKDAY function to find the last business day in excel. Enter the following formula in cell C5 to be able to do that.
=EOMONTH(TODAY(),0)-MAX(0,WEEKDAY(EOMONTH(TODAY(),0),2)-5)
🔎 How Does the Formula Work?
➤ WEEKDAY(EOMONTH(TODAY(),0),2)
The WEEKDAY function in the formula returns a serial of the day of the week. Here 2 means that Monday is the starting day of the week.
➤ MAX(0,WEEKDAY(EOMONTH(TODAY(),0),2)-5)
The MAX function returns the largest value in a set of values.
Read More: How to Convert Date to Day of Week in Excel (8 Methods)
7. Last Weekday of the Month from a Particular Date
Now, assume you have a given date in cell B5. Then you can apply the following formula in cell C5 instead. After that, you will get the last weekday of the month of that date as shown in the following picture.
=EOMONTH(B5,0)-MAX(0,WEEKDAY(EOMONTH(B5,0),2)-5)
Related Content: Excel Formula for Current Month and Year (3 Examples)
8. Last Business Day of the Previous Month in Excel
Assume you want to find the last business day of the month just passed. Then apply the following formula in cell C4 to get the required result as shown below.
=WORKDAY(EOMONTH(TODAY(),-1)+1,-1)
Read More: How to Calculate First Day of Previous Month in Excel (2 Methods)
9. Last Business Day of the Month Previous to a Given Date
Now, imagine you have a particular date in cell B5. You want to find the last business day of the previous month to that date. Then you need to enter the following formula in cell C5. After that, you will get the desired result as shown in the following picture.
=WORKDAY(EOMONTH(B5,-1)+1,-1)
Read More: How to Convert Date to Year in Excel (3 Quick Ways)
How to Find the Last Business Day of a Year in Excel
Suppose you want to find out the last business day of the year. The year is in cell B5. Then apply the following formula in cell C5. After that, you can use the fill handle icon to apply the formula to the cells below if you have a list of years instead.
=WORKDAY("1Jan"&(B5+1),-1)
Things to Remember
- The cells with date values are formatted as Long Date.
- Absolute references are used to enter holidays in the formula.
Conclusion
Now you know how to get the last business day of a month in excel. Please use the comment section below for further queries or suggestions. You can also visit our ExcelDemy blog to read more on excel. Stay with us and keep learning.
Related Articles
- Fix Excel Date Not Formatting Correctly (8 Quick Solutions)
- How to Change Default Date Format from US to UK in Excel (3 Ways)
- Stop Excel from Auto Formatting Dates in CSV (3 Methods)
- How to Convert 7 Digit Julian Date to Calendar Date in Excel (3 Ways)
- Convert a Date to dd/mm/yyyy hh:mm:ss Format in Excel
