The MONTH function is a simple date function that extracts the month from a given time. If you work with times but only care about the months, the MONTH function lets you eliminate everything else.
Remember that dates are just saved as numbers by your spreadsheet with date formatting applied.
Contents
Purpose
The MONTH function returns the month’s value from any given unit of time.
Syntax
=MONTH(time)
Common Errors
#VALUE! – An invalid date was passed to the function such as "1/32/2022". The month of January does not have 32 days.
#ERROR! – Formula parse error. The input to the function could not be used such as a date with too many slashes. – 1//21/2222.
#NUM! – Result was not a number. The number is too large to resolve to a date. – (548748511).
The output is an unexpected format such as 12:00 AM. – This means the formatting is incorrect, not the underlying number. Change the formatting to Number.
TIMEDIF – Google Sheets Add-On
Calculate any duration in:
- Years
- Months
- Weeks
- Days
- Hours
- Minutes
- Seconds
- Milliseconds
Examples
Example 1 – Plain and Simple
First, here are a few simple examples of the MONTH function.
| Formula | Description | Result |
=MONTH("10/5/2016") | Extract the month value from 10/5/2016. | 10 |
=MONTH("October") | Return the month of October. | #VALUE! |
=MONTH("October 8") | Extract the month value from October 8 | 10 |
Tip: Notice that the MONTH function can’t figure out what you mean by “October” unless you put a day value in front of it. See how dates and times work to make a little sense out of this.
Example 2 – Separating the Values of a Date and Time
Different date values examined using various functions.
| Time value | Tickmark | YEAR | MONTH | DAY | HOUR | MINUTE | SECOND | Formatted as number |
|---|---|---|---|---|---|---|---|---|
| 7/30/2016 | 1 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
| 7-30-2016 | 2 | 2016 | 7 | 30 | 0 | 0 | 0 | 42,581.00 |
| 6/1/2017 2:32 PM | 3 | 2017 | 6 | 1 | 14 | 32 | 0 | 42,887.61 |
| July 31, 2016 | 4 | 2016 | 7 | 31 | 0 | 0 | 0 | 42,582.00 |
| 12:30 PM | 5 | 1899 | 12 | 30 | 12 | 30 | 0 | 0.52 |
| 4/11/2017 11:24:14 | 6 | 2017 | 4 | 11 | 11 | 24 | 14 | 42,836.48 |
| 4/11/2017 | 7 | 2017 | 4 | 11 | 0 | 0 | 0 | 42,836.00 |
| 8 | 1899 | 12 | 30 | 0 | 0 | 0 | ||
| 1 | 9 | 1899 | 12 | 31 | 0 | 0 | 0 | 1.00 |
| 0.25 | 10 | 1899 | 12 | 30 | 6 | 0 | 0 | 0.25 |
| 43000 | 11 | 2017 | 9 | 22 | 0 | 0 | 0 | 43,000.00 |
- 1 A random date typed in using slashes.
- 2 A random date typed in using dashes. Note that it is processed the same way as the date with slashes.
- 3 Typed a random date in and added a time. Now the function extracts the hours and minutes.
- 4 Typed in a date but spelled out. Writing out the date also works, but it won’t work if you write the day as “31st” instead of 31.
- 5 If you only type in a time, the “zero” date is 12/30/1899.
- 6 Entered with the NOW function. The output will constantly change in the linked Google Sheet.
- 7 Entered with the TODAY function. The output will change daily in the linked Google Sheet.
- 8 A blank cell is December 30, 1899. Similar to tickmark 5.
- 9 Typing a 1 will increment the value in number 10 above by 1 day. In other words, “1=12/31/1899”. It is just a matter of how it is displayed, which can be controlled by going to the Format menu and choosing Number.
- 10 Just a decimal with no whole number increments the value by hours, minutes, and seconds instead of days.
- 11 To get near the current date, you need to start with 43,000 days away from 12/30/1899.
Live Examples in Sheets
Go to this spreadsheet for examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.
Hi,
is there a way to not have it do what it does when it’s blank? I want to use the data for a pivot table and this makes it not accurate for the month December?
Thanks for your help!
Best
Anna
Sorry, I’m not following. Post a link to your spreadsheet if you want me to look.
Had the same issue and fixed it with this workaround =if(B2=””,””,(text(B2,”MMMM”))) my month formula is different from the above article but I’m sure you could swap them
Hi, I am fairly new to sheets but I am trying to return a query when a row’s month equals a month of today function. I want data that only matches the current month. How would I do this?
It’s probably best to just apply a Filter and use the settings on the filter to narrow it down by month. Maybe take a look here. https://youtu.be/5S2y_7tzJZU
HI I have been having hard time … I want to generate a report of all my by month
=query(Data!A6:AB100,”Select * where A>= Date’2021-06-01′”).
If I want to get all the data from month of June ? My Data is stored in a column A its formatted as Date for example =6/09/2021. How can I just tell it to only get by the month ? I tried to tell between June 1 – 31th? It marks an error.
thank you for your videos btw 🙂
Why would this not work?
Column A| Custom format Mmm
Column Bl date format DD Mmm YY
Column A | =month(BN)
Column B l input dates
Unexpected actual results
A | B
Jan | 28 Dec 21
Jan | 29 Dec 21
Dec | 01 Jan 22
Jan | 04 Jun 08
It looks fine through your example. You can post a public link to your spreadsheet if you want.
A B
DATE MONTH
1/12/2023 1
2/9/2023 2
2/16/2023 2
12
12
12
12
Column A is date input, column B is =month(a2) formula and then copied down the line.
If Column A is empty, I am getting a value returned as 12.
I tried many IFERROR functions, but always get a parse error. I need Column B to be blank if Column A is blank, as I have count & sum functions by the month based solely on the input of A
Your help is greatly appreciated with this.
Brian
That makes sense given tickmark 8 in the tutorial above. Perhaps bury it in an IF statement. Something like =IF(ISBLANK(A1),””,MONTH(A1))
Formatting didn’t work in comment above, hopefully my explanation makes sense.