End of Month
Learn how to calculate month-end dates using functions across Excel, Google Sheets, and Power BI.
Overview
End of Month refers to the last day of a calendar month, commonly used in finance, accounting, and data analysis to calculate due dates, maturity dates, reporting periods, and more. Most spreadsheet and business intelligence tools provide specific functions to calculate month-end dates automatically.
Core Functions by Platform
Excel and Google Sheets: EOMONTH Function
The EOMONTH function (End of Month) is the primary function for calculating month-end dates in both Microsoft Excel and Google Sheets.
Syntax:
=EOMONTH(start_date, months)Parameters:
start_date: The starting date from which to calculatemonths: Number of months before (negative) or after (positive) the start_date
Examples:
=EOMONTH("15-Jan-2024", 0)returns 31-Jan-2024=EOMONTH("15-Jan-2024", 1)returns 29-Feb-2024 (leap year)=EOMONTH(TODAY(), -1)returns last day of previous month
Power BI: ENDOFMONTH Function
In Power BI's DAX language, ENDOFMONTH returns the last date of the month in the current context.
Syntax:
ENDOFMONTH(<dates>)Example:
ENDOFMONTH(DateTime[DateKey])Calculation Examples
Basic Month End Calculation
To find the last day of the month for any given date:
| Date | Formula | Result |
|---|---|---|
| 15-Jan-2024 | =EOMONTH(A2,0) | 31-Jan-2024 |
Previous Month End
To calculate the last day of the previous month:
| Formula | Result (if today is 15-Jan-2024) |
|---|---|
=EOMONTH(TODAY(),-1) | 31-Dec-2023 |
Next Month End
To calculate the last day of the next month:
| Formula | Result (if start date is 15-Jan-2024) |
|---|---|
=EOMONTH("15-Jan-2024",1) | 29-Feb-2024 |
Leap Year Handling
These functions automatically account for leap years. For example:
- February 2024 (leap year): Functions return 29-Feb-2024
- February 2023 (non-leap year): Functions return 28-Feb-2023
Common Use Cases
- Financial Reporting: Calculating period closes for month-end financial statements
- Project Management: Determining project milestone deadlines
- Inventory Control: Assessing monthly stock levels
- Subscription Billing: Calculating billing cycles and renewal dates
- Fiscal Period Calculations: Determining fiscal month ends regardless of calendar variations
Alternative Formulas
While EOMONTH is the simplest approach, you can also calculate month end using:
=DATE(YEAR(date), MONTH(date)+1, 0)This formula works by getting the first day of the next month and subtracting one day.
Key Takeaways
- Use EOMONTH in Excel and Google Sheets for month-end calculations
- ENDOFMONTH serves as the equivalent function in Power BI's DAX
- The functions automatically handle variable month lengths and leap years
- Month-end calculations are essential for financial reporting, project management, and date-based analysis
Sources: