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.
How to Find Last Business Day of a Month in Excel: 9 Handy Ways
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 return the last business day before the first day of the next month.
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)
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: How to Calculate Working Days between Two Dates in Excel
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 the 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 specifies that Friday to Saturday is a weekend day as opposed to the default Saturday to Sunday in the WORKDAY function.
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)
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.
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)
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)
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 Calculate Working Days in a Month in Excel
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.
Download Practice Workbook
You can download the practice workbook from the download button below.
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. Stay with us and keep learning.
