In this easy-to-follow guide, we'll explore the Excel MONTH and EOMONTH functions. These functions are super handy for working with dates. We'll show you how to use them to figure out which month a date belongs to, get the first and last day of a month, and more. Whether you're new to Excel or an experienced user, this tutorial will help you become a pro at handling dates in your spreadsheets. By the end, you'll be able to breeze through date-related tasks and make your Excel work even smoother. So, let's dive in and master these functions together!
MONTH and EOMONTH are built-in functions in Excel that assist users in performing date-based operations, particularly those tied to months. Let's delve into each function and see how they can be useful:
Excel MONTH Function - Syntax and Uses
The MONTH function is primarily used to extract the month from the date entered. It gives output in numbers ranging from 1 to 12.
Syntax for MONTH function
MONTH (serial_number)
Applications:
- Sales Analysis - Extracting month from sales date and summarizing sales data
- Academics and Research - For researchers collecting data over time to categorize their findings month-wise. Institutions can also plan academic schedules, holidays, and examinations months.
- Healthcare - Analyzing patient visitation trends.
How to Get Month Number from Date in Excel
The input to the MONTH function can be:
- A serial number from which a valid date can be extracted
- Reference to cells containing dates
- Date values returned from Excel formulas
Formula:
= MONTH (DATE(2022,6,7)) returns 6
As the date is 7th June 2022
= MONTH (D3) returns the month of date present in the cell D3
= MONTH ("07-June-2022") also returns 6
Example:
Variations of inputs that can be passed to MONTH
How to Extract Month Name from Date in Excel
This can be achieved by using the TEXT function. But it is only applicable to a specific type of data code - DD-MMM-YY where the month is written using letters.
Formula:
= TEXT(B2, "mmm") - it will return an abbreviated name of the month (upto three letters), like Jun for June
= TEXT(B2, "mmmm") - it will return a full month name i.e. November, December
Example:
Extracting Month Name from Date using TEXT FunctionHow to Convert Month Name to Month Number in Excel
This can be achieved by a combination of two functions - DATEVALUE and MONTH.
DATEVALUE converts a date stored as text to a serial number. While MONTH extracts the month number from the date.
Formula:
= MONTH (DATEVALUE(D3 & "1"))
- D3 is a cell containing the month name we want to convert
- &"1" is added to aid the DATEVALUE function to understand it's a date
Example:
Converting month name to month number using MONTH and DATEVALUE functionsHow to Convert Month Name to Number in Excel
Numbers 1-12 can be converted to their corresponding month names using the TEXT function. We need to multiply the number by 28 to convert it into a corresponding serial number understandable by the computer.
Formula:
= TEXT(B12 * 28, "mmm") will return the abbreviated month name (upto three letters) i.e. Apr for April
= TEXT(B12 * 28, "mmmm") will return the full month name i.e. November, December etc.
The month name can be extracted from a date format as well. For example:
= TEXT(DATE(2016, B12, 7), "mmm")
Here year and date are given whereas, B12 contains the month number. DATE will convert this information to a valid date code, from which TEXT will extract the month name.
Example:
Converting Month Number to Month Name using TEXT functionHow to Get the Last Day of a Month - EOMONTH Function
EOMONTH function denotes the End of the Month and is used to calculate the end date given a specified time interval and start date.
Syntax for EOMONTH Function:
EOMONTH (start_date, months)
Months denotes the number of months before or after the start date. Positive values are used for finding dates in the future while negative ones for the past.
Example
=EOMONTH(D3, 4) - returns the last day of month 4 months after the date in cell D3
=EOMONTH(D3, -4) - returns the last day of month 4 months before the date in cell D3
=EOMONTH("07-December-2023", 0) or =EOMONTH(DATE(2023,12,7), 0) - returns last date of current month
=EOMONTH(TODAY(), 0) also returns last day of the current month
Example Operations using EOMONTH FunctionNote - EOMONTH returns a serial number representing the resultant date. It can be converted into a comprehensive date
using the TEXT function. The TEXT function takes the value to be converted and date format type as its parameters.
Potential Errors that Can Occur
- #NUM! error can occur if the start_date is not a valid Excel date
- #VALUE error can occur if any of the supplied arguments are non-numeric.
- If a decimal value is provided for a month - the EOMONTH function will only take into account the integer part.
Applications
- Financial Modelling - This function finds its use in financial modeling, the process of finding a specific date can be efficiently automated with its help. It can be used in the monthly, quarterly, and annual financial models.
- Financial Analysts - It can also be useful for financial analysts when they have to calculate maturity dates for accounts payable or accounts receivable that fall on the last day of the month.
How to Get the First Day of a Month
There are many ways to get the first day of a month. Though there are various methods to find it, we can also make use of the EOMONTH function to get the first day. The various techniques are:
Get the 1st day of the month by the Month Number
We can make use of the DATE function to find out the first day of the given month number.
Formula:
= DATE(year, month number, 1)
We have to feed the current year, month number, etc. on our own in this case.
Example:
Finding first date of the month in which the given date liesGet the 1st day of the Month from a Date
If we want to calculate the first date in relation to the month provided in a specific date then we can employ both DATE and MONTH functions to do so.
Formula:
=DATE(year, MONTH(cell with the date), 1)
The formula will return the first day of the month based on the date fed to the MONTH function, be it referenced from a cell or a direct date code.
Example:
Finding first day of the month in which the dates in the mentioned cells lieFind the First Day of the Month Based on the Current Date
When we are required to work with the current date and cannot specify the required month or year, then we can make use of the EOMONTH and TODAY functions.
Formula:
= EOMONTH(TODAY(), 0) + 1 extracts the first day of the next month
= EOMONTH(TODAY(), -2) + 1 extracts the first day of the previous month
= EOMONTH(TODAY(), -1) + 1 extracts the first day of the current month
Example:
Finding the first date of the current month using the EOMONTH functionThe same task can also be performed using MONTH, DATE, TODAY, and YEAR functions. One can use the YEAR function to extract the year and MONTH to extract the month from the current date and feed it to the DATE function, with 1 set as the day.
In order to obtain the first dates of previous or next months one can just subtract or add 1 to the month number returned by the MONTH function.
Formula:
= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) returns the first date of the current month
= DATE(YEAR(TODAY()), MONTH(TODAY()) + 1, 1) returns the first date of the following month
= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1) returns the first date of the previous month
Example:
Finding the first date of the current month using DATE, YEAR, and MONTH functionsCalculating the Number of Days in a Month
Excel doesn't specifically provide a function to calculate the number of days in a given month. But there do exist a variety of functions that work with dates and times that we can employ for this purpose.
Get the Number of Days Based on the Month Number
If the month number and year are known, then we can use a combination of DAY and DATE formulae to get the number of days in that month.
Formula:
= DAY(DATE(year, month_number + 1, 1) -1)
How this formula works is, that the DATE function will return the first day of the following month, from which when 1 is subtracted, we get the last date of the month wanted. The DAY function will convert the date to a number.
Example:
Finding the number of Days in the required month by finding last date of the monthTo Get the Number of Days in a Month Based on Date
If the month number is not known, but we do know any date within that month, then the YEAR and MONTH functions can be used to extract the year and month number from the date. Then we just need to input these into the DAY/DATE formula and the no. of days will be calculated.
Formula:
= DAY(DATE(YEAR(A13), MONTH(A13) + 1, 1) -1)
Alternatively, the EOMONTH formula can be used to just return the last day of the month, which is further converted to the number of days by extracting the day.
Formula:
= DAY(EOMONTH(A1, 0))
Example:
Finding the Number of Days using YEAR, MONTH, and EOMONTH functionsHow to Sum Data By Month in Excel
We may want to get the sum of values for a particular month from tables that contain a large amount of data. Further problems may arise if the data is not in chronological order.
To solve this problem we can add a helper column that will convert dates to month numbers using a simple Excel MONTH formula.
The next step will be to list down numbers that are of interest in an empty column, and the sum values of each month using the following formula:
= SUMIF(D4:D14, F4, C4:C14)
Where F4 contains the desired month number.
Example:
Calculation of total sales per month using Helper ColumnA shorter and simpler method to perform the same task without the helper column will be to use the formula:
= SUMPRODUCT((MONTH(C4:C14) = F4) * (D4:D14))
Here column D contains dates, column D contains the values to sum and F4 contains the month number.
How to Conditionally Format Dates Based on Month
To take the calculations and visual presentations a step further, we can use the capabilities of Excel conditional formatting for dates using the Excel MONTH and EOMONTH functions.
Highlight Dates Within the Current Month
If we want to highlight all rows with the current month dates we can follow the following steps:
- Extract the month numbers from dates in a column using the simplest =MONTH(A15) formula.
- Compare these numbers with the current month given by =MONTH(TODAY())
- Based on what the formula returns (TRUE or FALSE), create an Excel conditional formatting rule
Formula:
= MONTH(A15) = MONTH(TODAY())
Example:
Step 1: Select the Data and Create a New Rule with the formula and formatting in Conditional Formatting Manager
Select the Data and Create a New Rule with the formula and formatting in Conditional Formatting ManagerStep 2: Apply the Formula to the selected range
Apply the formula to the selected rangeHighlighting Dates by Month and Day
If we want to highlight the gazetted holidays in our worksheet regardless of the year, for e.g. Diwali or New Year, then we can use the DAY and MONTH functions for the same.
Formula:
= AND (OR (DAY(A15) = 25, DAY(A15) = 31), MONTH (A15) = 12)
We use the DAY function to extract the day of the month (1-31) and the MONTH function to get the month number, and then check if the DAY is equal to either 25 or 31 and the MONTH is equal to 12.
Example:
Step 1: Create a New Rule in the Conditional Formatting Manager
Create a New Rule in Conditional Formatting ManagerStep 2: Specify the Formatting that needs to be applied to Cells for which the formula returns TRUE
Specify the Formatting that needs to be applied to Cells for which the formula returns TRUEStep 3: Select the range for which the formula needs to be applied and click Apply and OK
Select the range for which the formula needs to be applied and click Apply and OK
Example Use Case: Budget Planning for a Company
A company prepares its budgeting plans on a quarterly basis. However, within each quarter, the company also has to set aside specific funds at the end of each month for certain mandatory expenses. Here's how the EOMONTH function can be utilized in this context:
1. Determining Employee Bonus Allocation
Employees are given bonuses at the end of every quarter. To prepare for this, the financial analyst wants to know the exact end date of the month, 3 months from the current date, which denotes the end of the quarter.
If today's date, for example, is "15-January-2024", the formula in Excel would be:
=EOMONTH (TODAY(), 3)
Output:
45412 which represents 30 April 2024
2. Evaluating Quarterly Rent Payments
The company pays rent for its office spaces at the end of every quarter. The accounts department needs to know the last date of the month, 3 months prior, to ensure they've accounted for the past quarter's rent.
Assuming today's date is "15-April-2024", the formula would be:
=EOMONTH (TODAY(), -3)
Output:
45230 which represents 30 October 2023
3. Year End Closing
The company closes its financial books annually. To know the exact end date of the current financial year, the analyst can use the formula:
If the financial year starts from "1 April 2023", the formula would be:
=EOMONTH(DATE(2023, 4, 1), 12)
Output:
45412 which represents 30 April 2024
Similar Reads
VBA Date and Time Functions in Excel
Date and Time Functions are the inbuilt functions that give us the opportunity to see the date or time according to the user's need. Suppose a user needs to see the month or the day or the year then it can be easily seen by different date functions. Similarly, for the time function, also we can mani
5 min read
How to use CHOOSE Function in Excel
The CHOOSE function is technically part of Excel’s lookup function and can be incredibly useful. The CHOOSE function returns a value from a list using an index. One of those Excel features, CHOOSE, may not seem helpful alone, but when paired with other functions, it offers a ton of fantastic advanta
7 min read
Using CHOOSE Function along with VLOOKUP in Excel
In Excel, managing space is a very important point to consider. Now, let's suppose you are in a situation where you want to compare multiple columns in the Excel dataset, and you want to derive results, This is not possible using the VLOOKUP function alone. So, to make a function capable of doing so
2 min read
OFFSET Function in Excel With Examples
Excel contains many useful formulas and functions that make it more and more useful and at the same time user-friendly. Such a function is the OFFSET() function. In many cases, this function is also used inside another function. This function basically returns a reference of a single cell or a range
4 min read
How to Debug a User Defined Function in Excel VBA
Debugging a User Defined Function (UDF) in Excel VBA can feel like a detective mission where you search for clues to fix issues in your code. When a custom function doesn’t deliver the expected results, it can disrupt your workflow and lead to frustration. Fortunately, debugging tools in VBA make it
9 min read
How To Use MATCH Function in Excel (With Examples)
Finding the right data in large spreadsheets can often feel like searching for a needle in a haystack. This is where the MATCH function in Excel proves invaluable. The MATCH function helps you locate the position of a specific value within a row or column, making it a cornerstone of efficient data m
6 min read
How to use the IFS function in Excel
The IFS function in Excel acts as a keen partner, making choices for your information. It's perfect when you have different rules and wish to grant prizes based on those rules. Rather than getting misplaced in complicated enlightening, IFS assists your information in getting to where it ought to go.
7 min read
Excel CHOOSE Function with Array
We often use the CHOOSE function in Excel to pick something from a list. In this article, we'll see how to use CHOOSE with a bunch of things (we call it an array) to do various stuff in Excel. This array can be either many cells we put into the function or many things we get from the function. What
5 min read
Excel ROWS and COLUMNS Functions with Examples
In Microsoft Excel, where precision and efficiency reign supreme, mastering the art of maneuvering through cells and worksheets is indispensable. Two indispensable components in your Excel toolbox are the ROW and COLUMN functions, each meticulously crafted to execute discrete functions that can subs
7 min read
How to Insert a Function in Excel?
In MS Excel formula is an expression that help to calculate the value of a cell and in Microsoft Excel has many inbuilt in functions that we can use in our formula. Now if you want to insert a function in Excel or want to see all the functions by category, then in this series of MS Excel tutorial gu
4 min read