Find Last Monday of the Month Date in Excel (Formula)

We often need to find the date corresponding to the last given weekday of the month.

For example, you might need to know what date is the last Monday of the month falls, so that you can schedule a planned activity. 

In this tutorial, we will show you how to find the last Monday or any given weekday of the month in Excel using a single formula.

The formula involves mainly two Excel functions:

• EOMONTH
• WEEKDAY

We will also look at a more generalized formula that gives the last day in a month corresponding to any given weekday.

Finally, as a bonus, we will also show you how to find the last workday of the month in Excel (in case you need it).

Formula to Find the Last Monday of the Month Date in Excel

The best way to find the last Monday of the Month Date is to use a formula that involves the EOMONTH and WEEKDAY functions.

Before we look at the formula, let us first understand what these two functions do.

EOMONTH Function

The EOMONTH function is used to find the last day of the month for a given date.

It takes in a given date, along with an offset number of months, and returns the serial number corresponding to the last day of the month, that is the indicated number of months before or after the offset number.

The syntax for this function is:

EOMONTH(date_input, month_offset)

Here,

• date_input is the starting date. This should be a valid DATE value or a reference to a cell containing a valid date.
• month_offset is the number of months before or after the date_input. A positive number indicates a month in the future, while a negative number indicates a month in the past.

For example, the following formula returns the date of the last day of the month which comes 1 month after DATE(2021,2,28). In other words, it finds the last day of the month of March.:

=EOMONTH(DATE(2021,2,28),1)

If you want the last day of the same month, then the second parameter would have to be 0:

=EOMONTH(DATE(2021,2,28),0)

WEEKDAY Function

The WEEKDAY function is used to find the day of the week corresponding to a given date.

The function takes in a DATE value and returns a number between 1 and 7, to represent the day of the week. 

By default, this function returns 1 to represent a Sunday, 2 to represent a Monday, and so on.

However, you can customize this function to represent the order of the days of the week according to your requirement.

The syntax for the function is as follows:

WEEKDAY(date_input,[return_type])

Here,

• date_input is the date for which you want to find the day of the week. This should be a valid DATE value or a reference to a cell containing a valid date.
• return_type is an integer that determines the type of return value.

The second parameter is optional. As mentioned before this helps determine the sequencing of weekdays that you prefer.

For example, a value of 1 as return_type ensures that the sequence followed is from 1 (for Sunday) through 7 (for Saturday).

A value of 2 as return_type, on the other hand, ensures that the sequence followed is from 1 (for Monday) through 7 (for Sunday).

For example, the following formula returns the day of the week corresponding to DATE(2021,2,28).

=WEEKDAY(DATE(2021,2,28),2)

Since the return_type specified is 2, the day of the week, Sunday is represented with the integer 7.

This is because the sequence is 1 for Monday through 7 for Sunday when return_type=2.

By default, the return_type for the WEEKDAY function is 1.

This means if we omit the second parameter, Sunday is represented with the integer 1:

Now that we know what these two functions do, let’s put them together in a formula.

Using an Excel Formula to Find the Last Monday of the Month Date in Excel

Let’s say we have the following list of dates:

We want to find the date corresponding to the last Monday of the month for each of the given dates.

Here’s the formula that you can use (for cell reference A2):

=EOMONTH(A2,0)- WEEKDAY(EOMONTH(A2,0)-2)

When you press the return key, the above formula returns the date corresponding to the last Monday of the month for the date in cell reference A2.

Copy this formula down to the rest of the cells. Here’s the result you should get:

Note: If your formula displays a serial number instead of a date, then you can convert it to date by selecting the Short Date or Long Date format from the dropdown list in the Number group (under the Home tab) of the main menu. 

Explanation of the Formula

Let’s take some time to understand how this formula worked. We will go over the above formula part by part:

• EOMONTH(A2,0) – This formula finds the last day of the month that the date A2 falls in. Since January has 31 days, this formula returns the serial number corresponding to the date 1/31/2021.
• EOMONTH(A2,0) – 2 – This returns the date that comes 2 days before the last day of the month. We used the value 2 here to correspond to the second day of the week (which is Monday). The formula thus returns the date 1/29/2021.
• WEEKDAY(EOMONTH(A2,0) – 2) – This formula returns the day of the week corresponding to the date that is 2 days before the last day of the month. The formula basically helps us find how many days we need to roll back from the last day  of the month in order to get to the last Monday of the month. The value returned here is 6.
• EOMONTH(A2,0)-WEEKAY(EOMONTH(A2,0)-2) – Now all that we need to do is roll back the above number of days (6 in this example) from the last day of the month. This will give us the date corresponding to the last Monday of the month.

The final date returned is 1/25/2021. If you check the calendar for this date, you will see that this date is, in fact, the last Monday of the month of Jan 2021.

Generalizing the Formula

You could further generalize the formula to find the last weekday of the month, given any day of the week.

Finding the Last Tuesday of the Month

For example, to find the last Tuesday, you could replace the number 2 in the formula with the number 3 (corresponding to the third weekday, which is Tuesday). So your formula then becomes:

=EOMONTH(A2,0)- WEEKDAY(EOMONTH(A2,0)-3)

Finding the Last Wednesday of the Month

Similarly, to find the last Wednesday, you could replace the number 2 in the formula with the number 4 (corresponding to the fourth weekday, which is Wednesday). So your formula then becomes:

=EOMONTH(A2,0)- WEEKDAY(EOMONTH(A2,0)-4)

To generalize, for any weekday given in cell B2, the last corresponding weekday of the month for a given date can be found by the formula:

=EOMONTH(A2,0)- WEEKDAY(EOMONTH(A2,0)-B2)

Here are a few examples, with their results:

Also read: How to Calculate the Number of Months Between Two Dates in Excel?

Formula to Find the Last Working Day of the Month

If you were actually looking to find the last working day of the month, instead of a particular day of the week, there’s another Excel function to help you with that. 

The WORKDAY Function

The WORKDAY function is used to calculate dates that are a given number of working days before or after a given date.

For example, you can use it to find the date that is 5 working days after October 1st, 2021.

By working days, it takes into account any day other than Saturdays and Sundays, as well as any holidays that you specify. 

The syntax for the WORKDAY function is:

=WORKDAY (date_input, days_offset, [holidays])

Here,

• date_input is the starting date. This should be a valid DATE value or a reference to a cell containing a valid date.
• days_offset is the number of days before or after the date_input. A positive number indicates a date in the future, while a negative number indicates a date in the past.
• holidays are a list of dates that should be considered as non-working days. This parameter is optional

So to find the date corresponding to 5 working days after October 1st, 2021, we use the following formula:

=WORKDAY(DATE(2021,10,1),5)

The result we get is October 8th, 2021, because the WORKDAY function did not consider Saturdays and Sundays in the calculation (since they are non-working days).

Using the WORKDAY Function to Find the Last Working Day of the Month Date in Excel

To find the last working day of the month, you can use the WORKDAY function as follows (for cell reference A2):

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

When you press the return key, the above formula returns the date corresponding to the last working day of the month for the date in cell reference A2.

Copy this formula down to the rest of the cells. Here’s the result you should get:

Explanation of the Formula

Let’s take some time to understand how this formula worked.

We will go over the above formula part by part:

• EOMONTH(A2,0) – This formula finds the last day of the month that the date A2 falls in. Since January has 31 days, this formula returns the serial number corresponding to the date 1/31/2021.
• EOMONTH(A2,0) +1 – This returns the date that comes 1 day after  the last day of the month. In other words, it returns the date corresponding to the first day of the next month. The formula thus returns the date 2/1/2021.
• WORKDAY(EOMONTH(A2,0)+1),-1) – This formula returns the date that is one working day before the first day of the next month. In calculating this date, the WORKDAY function ignores any non-working days (like Saturdays and Sundays). So, the value returned (in this case) is 1/29/2021.

If you check the calendar for this date, you will find that the dates 1/30/2021 and 1/31/2021 are Saturday and Sunday respectively.

So the last working day for January 2021 is, in fact, 1/29/2021.

In this tutorial, we showed you how you can combine the EOMONTH and WEEKDAY functions into a formula that finds the last Monday of the month date in Excel.

We also showed you how you can generalize the formula to any day of the week.

In the end, we showed you how you can combine the EOMONTH and WORKDAY functions to calculate the last working day of the month date in Excel.

We hope this was helpful.

Other articles you may also like:

• How to Convert Days to Years in Excel
• How to Convert Month Number to Month Name in Excel
• How to Convert Date to Day of Week in Excel
• How to Add Days to a Date in Excel
• How to Convert Date to Month and Year in Excel
• Calculate Days Between Two Dates in Excel (Workdays/Weekends)
• How to Highlight Dates Before Today in Excel?
• How to Get the First Day Of The Month In Excel? (3 Easy Formulas)