How to Find Last Business Day of Month in Excel (9 Handy Ways)

Get FREE Advanced Excel Exercises with Solutions!

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


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)

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

Last Business Day of the Month (International)

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)

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.


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.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
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

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo