MONTH Function: A Brief The MONTH function in Excel is used to extract the month number (1 to 12) from a given input date. The MONTH function will return a #VALUE! error if the input is not a valid Excel date. It will return a #NUM! error if the input number is outside the acceptable range for Excel date values. MONTH month number (1 to 12) MONTH #VALUE! #VALUE! not a valid Excel date #NUM! outside the acceptable range Objective

Value Returned by function



Aim to return a valid month as number

MONTH Function will extract the month from the given input date and return the month number as the numerical value lies between 1-12! Objective

Value Returned by function



Aim to return a valid month as number

MONTH Function will extract the month from the given input date and return the month number as the numerical value lies between 1-12! Objective

Value Returned by function Objective Objective Value Returned by function Value Returned by function Aim to return a valid month as number

MONTH Function will extract the month from the given input date and return the month number as the numerical value lies between 1-12! Aim to return a valid month as number Aim to return a valid month as number MONTH Function will extract the month from the given input date and return the month number as the numerical value lies between 1-12! MONTH Function will extract the month from the given input date and return the month number as the numerical value lies between 1-12! MONTH Function: A Syntax =MONTH(date) =MONTH(date) date: This is the date from which you want to extract the month. It can be a cell reference containing a date, or a date written directly inside the formula using the DATE function. date: This is the date from which you want to extract the month. It can be a cell reference containing a date, or a date written directly inside the formula using the DATE function. date This MONTH Function was introduced in the Excel 2003 version. Excel 2003 Basic Example of using MONTH Function: In the below example, I’ve covered most of the common ways to use the MONTH function are shown using different types of input values. MONTH different types of input values Formula Used

=MONTH(B4) Formula Used

=MONTH(B4) Formula Used

=MONTH(B4) Formula Used Formula Used Formula Used =MONTH(B4) =MONTH(B4) S.no

Input Date

Output Result

Comments



1

10 March 2025

3

Valid date, returns month number



2

21 July 2023

7

Ignores the Time and extracts month value from the date



3

01 December 2024

12

Given date is a leap year, still extracts the month value



4

This is a Text

#VALUE!

The input is Text not a date – causes #VALUE! error



5

-999999

#NUM!

The input Number is too large for valid date S.no

Input Date

Output Result

Comments



1

10 March 2025

3

Valid date, returns month number



2

21 July 2023

7

Ignores the Time and extracts month value from the date



3

01 December 2024

12

Given date is a leap year, still extracts the month value



4

This is a Text

#VALUE!

The input is Text not a date – causes #VALUE! error



5

-999999

#NUM!

The input Number is too large for valid date S.no

Input Date

Output Result

Comments S.no S.no Input Date Input Date Output Result Output Result Comments Comments 1

10 March 2025

3

Valid date, returns month number 1 1 10 March 2025 10 March 2025 3 3 Valid date, returns month number Valid date, returns month number 2

21 July 2023

7

Ignores the Time and extracts month value from the date 2 2 21 July 2023 21 July 2023 7 7 Ignores the Time and extracts month value from the date Ignores the Time and extracts month value from the date 3

01 December 2024

12

Given date is a leap year, still extracts the month value 3 3 01 December 2024 01 December 2024 12 12 Given date is a leap year, still extracts the month value Given date is a leap year, still extracts the month value 4

This is a Text

#VALUE!

The input is Text not a date – causes #VALUE! error 4 4 This is a Text This is a Text #VALUE! #VALUE! The input is Text not a date – causes #VALUE! error The input is Text not a date – causes #VALUE! error 5

-999999

#NUM!

The input Number is too large for valid date 5 5 -999999 -999999 #NUM! #NUM! The input Number is too large for valid date The input Number is too large for valid date Example Explanation: First example: The function extracts the month number from a valid date. March is the 3rd month, so it returns numerical value 3.
Second example: Even if the input includes a time, the MONTH function looks only at the date part. Since the date is in July, it returns 7.
Third example: The input date falls in a leap year, but this does not affect how the month is extracted. Since it’s December, the function returns 12.
Fourth example: The input is plain text that does not follow a valid date format. So, the MONTH function returns a #VALUE! error.
Fifth example: The input is a negative number too low to be a valid Excel date. In this case, the MONTH function returns a #NUM! error. First example: The function extracts the month number from a valid date. March is the 3rd month, so it returns numerical value 3. First example: 3rd month 3 Second example: Even if the input includes a time, the MONTH function looks only at the date part. Since the date is in July, it returns 7. Second example: time MONTH date part July 7 Third example: The input date falls in a leap year, but this does not affect how the month is extracted. Since it’s December, the function returns 12. Third example: leap year not affect month December 12 Fourth example: The input is plain text that does not follow a valid date format. So, the MONTH function returns a #VALUE! error. Fourth example: text MONTH #VALUE! Fifth example: The input is a negative number too low to be a valid Excel date. In this case, the MONTH function returns a #NUM! error. Fifth example: negative number MONTH #NUM! Using MONTH Function with other Nested Functions: The following examples explains, how can we use the MONTH function with other Excel functions for multiple scenarios. Input Date : 10-03-2025 Input Date : 10-03-2025 S.no

Used Nested Functions

Scenarios

Output

Formula Used

Comments



1

TODAY, MONTH

Get the current month number

4

=MONTH(TODAY())

Returns the month of today’s system date



2

TEXT, MONTH

Convert to month name using input

March

=TEXT(C3,”mmmm”)

Returns full month name from the date in C2



3

IF, MONTH

Check if input month is March

Yes

=IF(MONTH(C3)=3,”Yes”,”No”)

Returns Yes if input month is March



4

DATE, MONTH

Extract month from custom date

12

=MONTH(DATE(2025,12,25))

Hardcoded date used inside formula, extracts month number



5

EDATE, MONTH

Add 2 months to input and get month

5

=MONTH(EDATE(C3,2))

Input March month + 2 months = May = 5



6

NOW, MONTH

Get month from current date and time

4

=MONTH(NOW())

Works the same even with time portion S.no

Used Nested Functions

Scenarios

Output

Formula Used

Comments



1

TODAY, MONTH

Get the current month number

4

=MONTH(TODAY())

Returns the month of today’s system date



2

TEXT, MONTH

Convert to month name using input

March

=TEXT(C3,”mmmm”)

Returns full month name from the date in C2



3

IF, MONTH

Check if input month is March

Yes

=IF(MONTH(C3)=3,”Yes”,”No”)

Returns Yes if input month is March



4

DATE, MONTH

Extract month from custom date

12

=MONTH(DATE(2025,12,25))

Hardcoded date used inside formula, extracts month number



5

EDATE, MONTH

Add 2 months to input and get month

5

=MONTH(EDATE(C3,2))

Input March month + 2 months = May = 5



6

NOW, MONTH

Get month from current date and time

4

=MONTH(NOW())

Works the same even with time portion S.no

Used Nested Functions

Scenarios

Output

Formula Used

Comments S.no S.no Used Nested Functions Used Nested Functions Scenarios Scenarios Output Output Formula Used Formula Used Comments Comments 1

TODAY, MONTH

Get the current month number

4

=MONTH(TODAY())

Returns the month of today’s system date 1 1 TODAY, MONTH TODAY, MONTH Get the current month number Get the current month number 4 4 =MONTH(TODAY()) =MONTH(TODAY()) Returns the month of today’s system date Returns the month of today’s system date 2

TEXT, MONTH

Convert to month name using input

March

=TEXT(C3,”mmmm”)

Returns full month name from the date in C2 2 2 TEXT, MONTH TEXT, MONTH Convert to month name using input Convert to month name using input March March =TEXT(C3,”mmmm”) =TEXT(C3,”mmmm”) Returns full month name from the date in C2 Returns full month name from the date in C2 3

IF, MONTH

Check if input month is March

Yes

=IF(MONTH(C3)=3,”Yes”,”No”)

Returns Yes if input month is March 3 3 IF, MONTH IF, MONTH Check if input month is March Check if input month is March Yes Yes =IF(MONTH(C3)=3,”Yes”,”No”) =IF(MONTH(C3)=3,”Yes”,”No”) Returns Yes if input month is March Returns Yes if input month is March 4

DATE, MONTH

Extract month from custom date

12

=MONTH(DATE(2025,12,25))

Hardcoded date used inside formula, extracts month number 4 4 DATE, MONTH DATE, MONTH Extract month from custom date Extract month from custom date 12 12 =MONTH(DATE(2025,12,25)) =MONTH(DATE(2025,12,25)) Hardcoded date used inside formula, extracts month number Hardcoded date used inside formula, extracts month number 5

EDATE, MONTH

Add 2 months to input and get month

5

=MONTH(EDATE(C3,2))

Input March month + 2 months = May = 5 5 5 EDATE, MONTH EDATE, MONTH Add 2 months to input and get month Add 2 months to input and get month 5 5 =MONTH(EDATE(C3,2)) =MONTH(EDATE(C3,2)) Input March month + 2 months = May = 5 Input March month + 2 months = May = 5 6

NOW, MONTH

Get month from current date and time

4

=MONTH(NOW())

Works the same even with time portion 6 6 NOW, MONTH NOW, MONTH Get month from current date and time Get month from current date and time 4 4 =MONTH(NOW()) =MONTH(NOW()) Works the same even with time portion Works the same even with time portion Example Explanations: First one:

This formula uses TODAY() inside MONTH() to return the current month number based on your system’s date. Since TODAY() always fetches the present date, MONTH(TODAY()) gives you the month part of that date.


Second one:

The TEXT() function is used here with a date cell to convert the date into a full month name like “March“. It doesn’t return the month number, but a readable month name, which is useful for headings or summaries.


Third one:

This formula checks if the month in cell C3 is equal to 3 (March). If true, it returns “Yes”, otherwise it shows “No”. This is useful for setting up conditions based on specific months in your data.


Fourth one:

Using the DATE() function, this formula creates a specific date, December 25, 2025. Then, MONTH() extracts the month part from that date, returning 12 for December.


Fifth one:

The EDATE() function moves the given date forward by 2 months. If the original date is in March (month 3), adding 2 months brings it to May (month 5), which MONTH() then returns.


Sixth one:

The NOW() function gives the current date and time. Even though the time is included, MONTH(NOW()) will still extract only the month number from the full timestamp. First one:

This formula uses TODAY() inside MONTH() to return the current month number based on your system’s date. Since TODAY() always fetches the present date, MONTH(TODAY()) gives you the month part of that date. First one: This formula uses TODAY() inside MONTH() to return the current month number based on your system’s date. Since TODAY() always fetches the present date, MONTH(TODAY()) gives you the month part of that date. This formula uses TODAY() inside MONTH() to return the current month number based on your system’s date. Since TODAY() always fetches the present date, MONTH(TODAY()) gives you the month part of that date. TODAY() MONTH() current month number TODAY() MONTH(TODAY()) month Second one:

The TEXT() function is used here with a date cell to convert the date into a full month name like “March“. It doesn’t return the month number, but a readable month name, which is useful for headings or summaries. Second one: The TEXT() function is used here with a date cell to convert the date into a full month name like “March“. It doesn’t return the month number, but a readable month name, which is useful for headings or summaries. The TEXT() function is used here with a date cell to convert the date into a full month name like “March“. It doesn’t return the month number, but a readable month name, which is useful for headings or summaries. TEXT() full month name March readable month name Third one:

This formula checks if the month in cell C3 is equal to 3 (March). If true, it returns “Yes”, otherwise it shows “No”. This is useful for setting up conditions based on specific months in your data. Third one: This formula checks if the month in cell C3 is equal to 3 (March). If true, it returns “Yes”, otherwise it shows “No”. This is useful for setting up conditions based on specific months in your data. This formula checks if the month in cell C3 is equal to 3 (March). If true, it returns “Yes”, otherwise it shows “No”. This is useful for setting up conditions based on specific months in your data. month C3 3 (March) “Yes” “No” conditions Fourth one:

Using the DATE() function, this formula creates a specific date, December 25, 2025. Then, MONTH() extracts the month part from that date, returning 12 for December. Fourth one: Using the DATE() function, this formula creates a specific date, December 25, 2025. Then, MONTH() extracts the month part from that date, returning 12 for December. Using the DATE() function, this formula creates a specific date, December 25, 2025. Then, MONTH() extracts the month part from that date, returning 12 for December. DATE() December 25, 2025 MONTH() 12 Fifth one:

The EDATE() function moves the given date forward by 2 months. If the original date is in March (month 3), adding 2 months brings it to May (month 5), which MONTH() then returns. Fifth one: The EDATE() function moves the given date forward by 2 months. If the original date is in March (month 3), adding 2 months brings it to May (month 5), which MONTH() then returns. The EDATE() function moves the given date forward by 2 months. If the original date is in March (month 3), adding 2 months brings it to May (month 5), which MONTH() then returns. EDATE() forward by 2 months March (month 3) May (month 5) MONTH() Sixth one:

The NOW() function gives the current date and time. Even though the time is included, MONTH(NOW()) will still extract only the month number from the full timestamp. Sixth one: The NOW() function gives the current date and time. Even though the time is included, MONTH(NOW()) will still extract only the month number from the full timestamp. The NOW() function gives the current date and time. Even though the time is included, MONTH(NOW()) will still extract only the month number from the full timestamp. NOW() current date and time MONTH(NOW()) month number That’s it. This tutorial is originally published on How to Use Excel MONTH Function? How to Use Excel MONTH Function

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Calculate Workdays in Excel With the NETWORKDAYS Function Like a Pro!

Using Minute Function to Extract Minutes From  Input Time Like a Pro

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Quickly Extract Month Values in Excel with the MONTH Function

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps