MONTH Function – Google Sheets

The MONTH function is a simple date function that extracts just the month from a given time. If you are working with times, but only care about the months, the MONTH function lets you get rid of everything else. Remember that dates are really just saved as numbers by your spreadsheet with date formatting applied.


The MONTH function returns the value of the month from any given unit of time.

Video Explanation




Example 1 – Plain and Simple

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 of the of 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 that 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
6/1/2017 2:32 PM32017611432042,887.61
July 31, 20164201673100042,582.00
12:30 PM518991230123000.52
4/11/2017 11:24:146201741111241442,836.48
  • 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 notice the hours and minutes being extracted.
  • 4 Typed in a date but spelled out. This also works, but it won't work if you write the day as "31st" instead if 31.
  • 5 If you only type in a time, the "zero" date is 12/30/1899.
  • 6 Entered with the NOW function. This will change constantly in the linked Gooogle Sheet.
  • 7 Entered with the TODAY function. This will change daily in the this linked Gooogle Sheet.
  • 8 A blank cell is December 30, 1899. Similar to tickmark 5.
  • 9 Typing a 1 increments the value in number 8 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 present date, you need to start with 43,000 days away from 12/30/1899.

 Live examples in Sheets

Go to this spreadsheet for the examples of the YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND functions shown above that you can study and use anywhere you would like.

0 thoughts on “MONTH Function – Google Sheets

  • 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!

    • 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?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>