Get the last working day of the month using excel

Mira needs to pay her bills on the last working day of the month and would like to know the last working day for the current month

To get the last day of the current month we will break it into 4 steps

1) Get the current date

= today()

2) Get the last date of the month using EOMONTH

=EOMONTH(today(),0)

3) Move to the 1st of the next month by adding one day to it

=EOMONTH(today(),0) + 1

4) Move back one business day using WORKDAY

=WORKDAY(EOMONTH(today(),0) + 1,-1)

 

However, for working days you may want to exclude the public holidays too.

Write down the public holidays in the year  in adjacent columns or rows. Select those cells and give it a name like PublicHolidays.  (To name a range of cells, select the cells and type the name in the Name Box, which is just above the excel sheet , under the menu, on the extreme left)

Now change the WORKDAY function to include the holidays

=WORKDAY(EOMONTH(today(),0) + 1,-1,PublicHolidays)

 

You can use it to find the last working day of any month. If you want to use it for for a different date, replace today() with a reference to a cell that contains the date.

=WORKDAY(EOMONTH(A1,0) + 1,-1,PublicHolidays)

Leave a Reply

Your email address will not be published. Required fields are marked *