Updated June 9, 2023
MONTH in Excel
The month function in Excel is one of the simplest functions to understand, which only returns a month out of any selected dates. We can select the cell containing a date or enter the month number we want to see. But using the Month function by selecting any date and getting the month number out is the best way to use it.
MONTH Formula in Excel:
The Formula for the MONTH Function in Excel is as follows.
The MONTH function uses only one argument where the serial number argument is the date that you want to return the month.
DAY Formula in Excel:
The Formula for the DAY Function in Excel is as follows.
Arguments :
date_value/ Serial_number: A valid date to return the day.
Returns:
The DAY function returns a numeric value between 1 and 31.
YEAR Formula in Excel:
The Formula for the YEAR Function in Excel is as follows.
Arguments:
date_value/Serial_number: A valid date to return the month.
Returns:
The YEAR function returns a numeric value between 1999 and 9999.
Steps to Use Month Function in Excel
MONTH Function in Excel can be used as part of a formula in a cell of a worksheet. Let’s consider the below example for a good understanding. We cannot enter 10/05/2018 directly into the cell. Instead,d we need to enter “10/05/2018”. Excel will automatically convert dates stored in cells into serial format unless the date is entered in text.
After entering the cell, the formula input appears below; the cell is shown below for reference. We can use the shortcut “Insert Function Dialog Box” for detailed instructions:
We will get a below dialogue box to select the specific cell where we have given Month, Date Year.
Select the B2 Cell.
Give ok so that we will get the exact month’s value.
Result:
Using Choose & Today Function
Using the month function, we can use the choose & Today function to get the exact month name, wherein the above example shows we have used only the MONTH function to get the month value. In the below example, we have used the month function along with CHOOSE and TODAY.
This dynamic formula will return the month’s name instead of the month number.
How to Use MONTH Function in Excel?
MONTH Function in Excel is very simple and easy to use. Let us now see how to use the MONTH Function in Excel with the help of some examples.
Example #1
To find out today’s month, we can use the below formula:
=MONTH(TODAY())
Which will return the current today month.
Let’s see to extract the monthly value with the below example
Example #2
In the above example, we retrieved the exact month using the Month function to get the month name.
Example #3
In certain scenarios, we can see Year, a month, and a day will be given. In this case, we cannot use the MONTH function. We can use the Month and date functions to get an accurate result.
Date Function:
Formula:
DATE( year, month, day )
The year, month, and day arguments are integers representing the year, month, and day of the required date.
In the below example, we have used the Month Function and the date function to get the proper result.
The Result will be:
Using the End of Month Function in Excel
If we need to find out the end of the month, EOMONTH can be useful to find out exactly.
To calculate the last day of a month based on a given date, you can use the EOMONTH function. Let’s see the below example works.
So in the above example, we can see A1 columns, which have a day, Month & Year, and B1 Columns, showing the Last day of the month using the EOMONTH function.
We can drag the formula using Ctrl + D or double-click on the right corner of cell B2.
This way, we can easily extract the end of the month without using a calendar.
How does the EOMONTH Formula Works?
This EOMONTH Function allows you to get the last day of the month in the future or past month. If we use Zero(o) for months, EOMONTH will return on the last day of the month in the same month, as seen in the above example. To get the last day of the prior month, we can use the below formula to execute:
Formula:
=EOMONTH(date,-1)
To get the last day of the next month, we can use the below formula to execute:
Formula:
=EOMONTH(date,1)
Alternatively, we can use the Date, Year, and Month function to return the last day of the Month.
Formula:
=DATE(YEAR(date),MONTH(date)+1,0)
So in the above example, we can see various months in the A1 columns, and B1 shows the Last day of the month.
This way, we can easily extract the end of the month without using a calendar.
Date Function Arguments
The Excel date function is a built-in function in Excel that will come under the Date/Time Function, where it returns the serial date value for a date.
A formula for DATE Function:
=DATE( year, month, day )
Arguments:
Year: A number between 1 and 4 digits representing the year.
Month: This represents the month value; if the month value is greater than 12, then every 12 months will add1 one year to the year value.
Day: This represents day Value. If the day value exceeds the days, the exact number of months will be added to the month value.
Example
If we enter the date by default, Excel will take a general format which is shown below:
It will take a general format which is shown below:
So to get the exact date to be displayed, we have to choose the format cells and then choose the day, month & year format.
In the above example, we have formatted the cell to get the appropriate date, month & year.
Common Errors We Will Face While Using Month Function in Excel:
Excel will take an integer value because of the “General format” if we normally enter the date in the cell by default.
So whenever we wish to update the date in the cell, we need to format the cell and choose the appropriate date, month, and Year format.
Things to Remember
- The date you want to get the month number should be valid.
- If you mention an invalid date, it will return #VALUE! Error.
- If you skip entering any value in serial_number, it will return.
Recommended Articles
This has been a guide to MONTH in Excel. Here we discuss the MONTH Formula in Excel and how to use MONTH Function in Excel, along with Excel examples and downloadable Excel templates. You may also look at these useful functions in Excel –
