In this article, you will learn how to use the EOMONTH function in Google Sheets. This function is beneficial for checking a month's end date and showing months, such as table headers by month (e.g., monthly financial statement or monthly KPI tracker, etc.).
The generic syntax of the function is as follows:
Start_date: This should be a date. This date is a starting point from which the formula calculates the result.
Months: The number of months before (negative figure) or after (positive) the “start_date”. For example, if you enter “3/1/2022” for “start_date” and “2” for this argument, the formula returns the end date of May 2022, as three plus two is five.
As the function shows the end date of a month and adjusts the date shown monthly, it is helpful when you want to know the end date of a month or show months consecutively for a period (e.g., table headers for monthly data). Assume you are a finance manager and need to create a monthly financial and KPI dashboard for the first half of the fiscal year ending December 31, 2022. You want to make table headers by month. You can use this formula instead of typing all months for your data set.
Start_date: For January 2022, we input it manually. Don’t forget to enclose the date with quotation marks. For each month from February to June, we refer to the next cell to the left. (e.g., cell B2(=1/31/2022) in the case of the formula for February (cell C2).)
Months: We input 0 for January because we input a date in January for “start_date” and want to know the end date of January. For each month in the rest of the period, we input “1” because we want to show the end date of a month, which is a month after the previous month.
In this example, we don’t care about the exact date but only the months. So, we are going to change the format of the cells.
Steps 3 to 5
After all steps
The format change above is just an example. You can customize the formatting of cells and dates according to your company’s coloring order or your preference.
Read this article to understand how the HLOOKUP function works in Google Sheets.
Check this article to learn how to use the DATE formula in Google Sheets.
You can use the WORKDAYS or NETWORKDAYS functions depending on your purpose.
If you don’t get the right answer to your question in this article or you have other questions related to date and/or time, you may find the answers in the following articles.
Formulas related to dates
How to Use EDATE Function in Google Sheets - understand a date before/after a specific number of days
DATEVALUE Function in Google Sheets: Explained - convert a date into a serial number
Formulas related to time
How to Insert Current Time in Google Sheets - NOW function
Are you learning this formula to build a financial model or financial KPI dashboard? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.
To learn more about LiveFlow, book a demo.
Learn how to do this step-by-step in the video below 👇