How to get last day of month in google sheets (Examples) 2022

In this article, you will learn how to get the last day of month in google sheets.  

To get the last day of the month in google sheets, use the EOMONTH formula. 

To get the last day of next month you can write the formula as =EOMONTH(A1, 1)

To get the last day of the 6th month from today, you need to write =EMONTH(A1, 6)

Here is a demo showing this in action:

EOMONTH formula – Last day of month in google sheets

Syntax

EOMONTH(start_date, months)

Input

start_date = the date from which to calculate the result

months = number of months before or after the start date

Output

Returns a date that represents the last day of the month as per conditions provided in the EOMONTH formula

Example – Get the last day of month in google sheets

Let’s see one real-life example.

You have a list of joining dates of a few employees in column A. These employees will get a confirmation email and a small incentive on the last day of the third month from their joining date.

Below is the data:

To get the last day of the third month, you can use the EOMONTH formula in google sheets as shown below:

=EOMONTH(start_date, 3)

Below is the final result after applying the formula.

As you can see from the above,

John who joined on X date will receive the incentive on Y date

Steve who joined on X date will receive the incentive on Y date

Ana who joined on X date will receive the incentive on Y date

Kelly who joined on X date will receive the incentive on Y date

Example – get last day of the month in google sheets 3 months ago

Let’s say you have a list of dates in column A. You want to get the last day of the month which is 3 months before the current date.

Here we are calculating the months before so we will pass a negative number to the EOMONTH formula.

=EMONTH(start_date, -3)

Below is the screenshot showing this in action:

Last day of the month in google sheets inside formula

If you want to get the last day of the month and also include this in a sentence, you can use the concatenate formula to achieve this.

Here is an example:

="Last day of the month is:" & TEXT(EOMONTH(A2, 0), "mm/dd/yyyy")

Below is a screenshot showing this in action:

The date format we specified here is “mm/dd/yyyy”. You can specify many other formats of dates as you like.

Get the first day of the month in google sheets

Till now you have seen how to get the last day of the month. Now let’s see how to get the first day of a month.

To get the first day of the current month in google sheets, you can use the EOMONTH formula to get the last day of the previous month and add 1 to it.

The formula to get the first day of the current month is =EOMONTH(TODAY(), -1)+1

Let me explain this with an example.

Below we have a list of dates in column A. In column B, we have a list of month counts before or after which we need the first date. 

In column C, we have used the EOMONTH formula to get the desired result:

Get the last Monday of the month in google sheets

Since Monday to Friday is a typical work week and the day starts on Monday, sometimes you want to know the last Monday or Friday of a month.

If you have the date in cell A1, then use the below formula to find the last Monday of the current month:

=EOMONTH(A1,0)-(WEEKDAY(EOMONTH(A1,0),2))+1

To find the last Friday of the month, use:

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

Example:

In the below screenshot, we have a list of dates in column A. For each date in column A, we want to find the last Monday and last Friday in columns B and C respectively.

As you can see from the above, using the custom EOMONTH formulas, we can tell which date will be the last Monday or Friday. 

FAQ

Wrapping up

In this tutorial, we learned how to get the last day of month in google sheets. Also, we saw how to calculate the last day of the previous month, the first day of any month, the last Monday of any month, and the last Friday of any month.

Go ahead and use this in your worksheet.

Appendix

[1] EOMONTH formula to get last day of month in google sheets – Link

Further Reading

New to google sheets ? Start here

More related to Formulas:

Learn more about Google sheets Formulas.
Logical functions in google sheets
Error handling in google sheets