Last Working Day of the Month

Excel Formula to calculate Last Working Day of the Month:

It is simple in Excel to calculate Last Day of the Month with the help of EOMONTH Formula. However, it is slightly tricky to figure out Last WORKING Day of the month. Four different Excel Formulas are demonstrated below to calculate last working day of the month.

Four Methods to calculate Last Working Day of the Month:

Below example works with the scenario where Saturday’s & Sunday’s are holiday. Also, Method 3 and 4 can be modified to exclude any day that falls in the holiday list. As of now, below examples works to exclude only Saturday and Sunday. Date is entered in Cell C3 and 4 Methods are shown below to derive Last Working Day of the Month with reference to date entered in cell C3.

Calcuate Last Working Day of the Month

Calcuate Last Working Day of the Month

Method – 1:

Formula:=IF(WEEKDAY(EOMONTH(C3,0))=7,EOMONTH(C3,0)-1,IF(WEEKDAY(EOMONTH(C3,0))=1, EOMONTH(C3,0)-2,EOMONTH(C3,0)))

As per default Weekday Function, Saturday is Day Number 7 and Sunday is Day Number 1. Excel IF Function has been used to reduce 2 days if the last day is Sunday and 1 day is if the last day is Saturday. No change is made if the last day is already a working day. Also, EOMonth is used to figure out the last day of the month.

Method – 2:

Formula: =EOMONTH(C3,0)-CHOOSE(WEEKDAY(EOMONTH(C3,0),2),0,0,0,0,0,1,2)

Excel Function CHOOSE and WEEKDAY is used to figure out number of days that needs to be reduced from the Last day of the month. As discussed in Method – 1, as per the default Weekday Function, Saturday and Sunday is day number 7 and 1 respectively. It can be changed by mentioning [return type] in weekday function. Accordingly, the return type 2 in weekday function will result as day number 6 and 7 for Saturday and Sunday respectively. Further, Choose function is used to reduce 1 and 2 if the last day is Saturday and Sunday respectively.

Method – 3:

Formula: =WORKDAY(DATE(YEAR(C3),MONTH(C3)+1,1),-1)

Excel Function DATE, YEAR, MONTH is used to get the 1st working day of the following month. Subsequently, Excel WORKDAY Function is used to do -1 working day to get the Last Working Day of the month. This method can also be used to exclude other holidays in order to get the Last Working Day of the month.

Method – 4:

Formula: =WORKDAY(WORKDAY(EOMONTH(C3,0),1),-1)

It is similar to Method 3. First working day of following month is calculated with Excel Function WORKDAY and EOMONTH. Thereafter, -1 Working day is done to get Last Working day of the month. This method can also be used to exclude other holidays in order to get the Last Working Day of the month.


Categories: Formulas, Tips and Tricks

Tags: , , , ,

Leave a Reply

%d bloggers like this: