The EOMONTH function in Google Sheets is a commonly used date function. It is often used with other date functions such as WEEKDAY and WORKDAY. You can also see its use with SEQUENCE and QUERY.

What is its role?

The EOMONTH function can be used to return the last day of the month that is n months before or after a given date.

For example, if you want to generate dates for a Gantt chart, you could use the EOMONTH function to create a range of dates that represents the entire project timeline.

Some complex use cases of this function involve:

• With SEQUENCE, it can populate a full 12 months of month-start or month-end dates, which can be used as row headers.
• QUERY can group a date column by month number, but with the support of EOMONTH, we can group by month text.
• Use it with WORKDAY or WORKDAY.INTL functions to find the first or last working day of a given month.
• With WEEKDAY, you can find a specific end-of-the-month day.

Syntax and Arguments

Syntax of the EOMONTH Function: EOMONTH(start_date, months)

Arguments:

start_date: The date from which to calculate the end of the month date.

months: The number of months before (-ve) or after (+ve) start_date to consider.

Tip: Enter 0 to get the end of the month of the specified date, 1 to get the end of the month after the specified date, and -1 to get the end of the month before the specified date.

In the following example, cell A1 contains the date January 1, 2023. The following formula in cell B1 will return the date December 31, 2023.

=EOMONTH(A1,11)

The following formula in cell C1 will return the date December 31, 2022.

=EOMONTH(A1,-13)

How to Get the Last Day of a Month in Google Sheets

The EOMONTH function is for returning the last day of a month from a date. However, we can adjust it to return the month-end date from a month text or month number. We will see all of these examples below.

In the following example, I have a date in cell A1, a month number in cell A2, and a month text in cell A3.

1. End of the Month Date from a Date:

=EOMONTH(A1,0)

2. End of the Month Date from a Month Number:

=EOMONTH(DATE(2023,A2,1),0)

Note: The DATE function converts a provided year, month, and day into a date. The syntax is: DATE(year, month, day)

3. End of the Month Date from a Month Text:

=EOMONTH(DATE(2023,MONTH(A3&1),1),0)

You May Like:- Formula to Convert Month Name in Text to Month Number in Google Sheets.

How to Get the First Day of a Month in Google Sheets

We can use the EOMONTH function in Google Sheets to return the last day of the specified date. However, if you use a negative value for the months argument, such as -1, the function will return the last day of the previous month. You can then add 1 to the returned date to get the first day of the specified date.

1. Month Start Date from a Date:

=EOMONTH(A1,-1)+1

2. Month Start Date from a Month Number:

=EOMONTH(DATE(2023,A2,1),-1)+1

3. Month Start Date from a Month Text:

=EOMONTH(DATE(2023,MONTH(A3&1),1),-1)+1

I know you are clever enough to understand the examples, so I am not attempting to detail every bit of the formulas that could eventually confuse you.

EOMONTH Function with SEQUENCE in Google Sheets

When you create a Gantt chart or a KPI dashboard, you may want a header row with month names.

For example, you can enter “Jan” in cell B1 and “Feb” in cell B2, and then select both cells and drag the fill handle across until cell M1. Alternatively, you can hand-enter the month names in the B1:M1 range.

Instead of using month text, you can create month start dates and format them to look like month text. Here’s how to do it:

For example, if cell A1 contains the title “Name” and cells B1:M1 are designated for months, you can use the following formula in cell B1:

=ARRAYFORMULA(EOMONTH(DATE(2023,1,1),SEQUENCE(1,12,-1))+1)

How does this EOMONTH and SEQUENCE combo formula work?

The EOMONTH function in Google Sheets can only return a single result. To return an array of results, we used the ARRAYFORMULA function.

The rest of the inputs are simple.

1. start_date: DATE(2023,1,1) – returns the date January 1, 2023.
2. months: SEQUENCE(1,12,-1) – returns the numbers {-1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10}.

This generates month-end dates from December 2022 to November 2023. The +1 makes the difference. It helps the formula to return month start dates from January 2023 to December 2023.

This combination of the EOMONTH and SEQUENCE functions will return the month start dates from January 2023 to December 2023.

You can format it to display as month texts while keeping the underlying values as proper dates. Here’s how:

1. Select cells B1:M1.
2. Go to the Format menu > Number > Custom number format.
3. In the Type field, enter mmm.
4. Click Apply.

The above EOMONTH function generates a sequence of month start dates, which has two main advantages over entering text manually.

1. It is easy to format. You can apply a custom number format to display the month and year, such as mmm-yy.
2. The underlying values are dates, so you can use them for comparison. For example, when using the sequence as a Gantt chart timescale, you can check whether a task start date falls between January (B2) and February (C2).

EOMONTH Function with WEEKDAY in Google Sheets

In one of my earlier tutorials, I explained how to find the date corresponding to the last Saturday of the given month. In that, I used the EOMONTH function with the WEEKDAY function.

Here is a more generalized formula that you can use to find any last weekday or weekend of a given month.

Steps:

1. In a cell (B2 as per example), enter the month start date of any month. For example, enter 1/12/2023.
2. In cell C2, insert the following formula:

=TO_DATE(LET(wd,1,dt,SEQUENCE(7,1,EOMONTH(B2,0),-1),FILTER(dt,WEEKDAY(dt)=wd)))

This formula will return the last Sunday of the specified month. To get the last Monday of the specified month, you should replace 1 (highlighted in red) with the corresponding weekday number, i.e., 2.

It is the most versatile formula to find the last Sunday, Monday, or any last weekday or weekend of any given month in Google Sheets. The weekday number can be 1 for Sunday, 2 for Monday, 3 for Tuesday, 4 for Wednesday, 5 for Thursday, 6 for Friday, and 7 for Saturday.

That means, to get the last Friday of a given month, you can use the below formula. Don’t forget to enter the month start date in cell B2.

=TO_DATE(LET(wd,6,dt,SEQUENCE(7,1,EOMONTH(B2,0),-1),FILTER(dt,WEEKDAY(dt)=wd)))

How does this EOMONTH, WEEKDAY, and FILTER combo work?

There are two key parts to this formula: SEQUENCE [SEQUENCE(7,1,EOMONTH(B2,0),-1)] and FILTER [FILTER(dt,WEEKDAY(dt)=wd)].

1. The SEQUENCE part returns a sequence of dates, starting 7 days before the month-end date of the date in cell B2.
2. The FILTER part filters the dates returned by the SEQUENCE function to only those that match the specified WEEKDAY number.

The LET function in the original formula names the SEQUENCE part with dt and WEEKDAY number with wd. So, the FILTER part could be written as: =FILTER(dt,WEEKDAY(dt)=wd)

EOMONTH Function with WORKDAY in Google Sheets

I have already posted how to use the EOMONTH function with the WORKDAY.INTL function in Google Sheets. In that post, I showed how to find the first and last workdays of a month. You can use the WORKDAY function instead of WORKDAY.INTL in most cases.

The Role of EOMONTH in QUERY

The role of the EOMONTH function in the QUERY function in Google Sheets is primarily related to grouping a date column by month or month and year.

Of course, the QUERY function doesn’t strictly require the EOMONTH for such grouping. But the problem is QUERY uses month numbers. Here is an example.

=QUERY({A1:C12},"Select month(Col1)+1,Col2,sum(Col3) group by month(Col1)+1,Col2",1)

The formula sums column C (Col3) by grouping column A (Col1) by month and column B (Col2) by item.

QUERY selected month from column A (Col1), and items from column B (Col2) for grouping. Added 1 to month number because QUERY month numbers are 0-11, not 1-12.

Now let’s see how to use the EOMONTH function with the QUERY function in Google Sheets.

QUERY Syntax: QUERY(data, query, [headers])

In the above example, the QUERY data is A1:B12. We should rewrite that with virtual data that contain the EOMONTH function. Here are the steps.

We will use the following EOMONTH function to convert dates in A2:A12 to the beginning of the month dates:

=ARRAYFORMULA(EOMONTH(A2:A12,-1)+1)

Append it with the items and amount

={ARRAYFORMULA(EOMONTH(A2:A12,-1)+1),B2:C12}

The above is the new virtual data, and here is the QUERY formula. In this, I have not used the QUERY MONTH function. In addition to that, another major change is the FORMAT clause.

=QUERY({ARRAYFORMULA(EOMONTH(A2:A12,-1)+1),B2:C12},"Select Col1,Col2,sum(Col3) group by Col1,Col2 format Col1'mmmm-yy'",0)

Some of you may ask why should I use the EOMONTH function when I can use the TEXT function to convert dates to month text straightaway, like this:

=ARRAYFORMULA(TEXT(A2:A12,"mmmm-yy"))

There is one specific reason. If you use this formula, the months in column E will be in alphabetical order, not by month and year order.

Conclusion

In this tutorial, I have covered basic to advanced use of the EOMONTH function in Google Sheets. You can use it in many other scenarios depending on your needs.

For example, when you are unsure whether 2020 is a leap year and want to specify the end of the month date of February, you can use the following formula:

=EOMONTH(DATE(2020,2,1),0)

This is because the EOMONTH function will always return the last day of the month, regardless of whether the month has 28, 29, 30, or 31 days.