Last Business Day of the Month in Excel (9 Examples)

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.

Last Business Day of the Month in Excel


Download Practice Workbook

You can download the practice workbook from the download button below.


Last Business Day of 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)

Last Business Day of Current Month

🔎 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 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)

Last Business Day of the Month (International)

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:


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)

Last Business Day of Current Month with WEEKDAY Function

🔎 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)


Last Business Day of the Year in Excel

Suppose you want to find out the last business day of a 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


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

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo