Google Sheets Month: Master Month Calculations in 2026
Learn how to extract and format the month in Google Sheets using MONTH, TEXT, and related functions. Step-by-step examples, Apps Script tips, and practical dashboards to simplify monthly reporting in 2026.
Google Sheets month means extracting the month component from a date and using it in calculations or labels. Core functions include MONTH, TEXT, and EOMONTH, with DATEVALUE for converting text to dates. Example: =MONTH(A2) returns 1–12, and =TEXT(A2, "mmmm") returns the full month name, enabling monthly summaries in 2026. See the full guide for deeper examples.
Understanding month extraction in Google Sheets\nMonth extraction is a common operation when you want to group data by calendar month, create time-based dashboards, or prepare monthly reports. In Google Sheets, the month component of a date can be numeric (1-12) or textual (January, February, etc.). The two most used functions are MONTH, which returns a number, and TEXT, which formats a date into a string representation of the month. The combination of YEAR and MONTH can anchor a date to a specific month, while EOMONTH returns the last day of a given month. This section covers practical usage, edge cases, and how to choose the right approach for your project.\n\nCode examples:\n\nexcel\n=MONTH(A2)\n\n\nexcel\n=TEXT(A2, \"mmmm\")\n\n\nexcel\n=TEXT(A2, \"yyyy-mm\")\n\n\nLine-by-line explanation: 1) MONTH(A2) converts a date in A2 to a numeric month; 2) TEXT(A2, "mmmm") yields the full month name; 3) TEXT(A2, "yyyy-mm") creates a sortable year-month label; 4) When dates are stored as text, DATEVALUE or TO_DATE can help. Edge cases: invalid dates return errors; fix with IFERROR.
titleLengthExceededWarning":false},
Steps
Estimated time: 15-25 minutes
- 1
Prepare your data
Ensure dates are in a dedicated column (e.g., column A) and that numeric values to aggregate are in another column (e.g., column C). Create headers that clearly name the columns to avoid confusion later.
Tip: Label columns clearly (e.g., Date, Amount) to simplify month-based formulas. - 2
Add a month helper column
In a new column, compute a normalized month anchor, such as the first day of the month, to enable reliable Aggregation by month.
Tip: Use a consistent date anchor like the first day of the month to keep joins simple. - 3
Create month labels and aggregates
Generate year-month labels with TEXT and group sums with SUMIF/QUERY to produce a monthly summary table.
Tip: Prefer a stable year-month label (yyyy-mm) for reliable sorting. - 4
Validate and handle text dates
Convert text dates to real dates using DATEVALUE or TO_DATE when needed, and guard with IFERROR to avoid crashes.
Tip: Always test with a few edge dates (end-of-month, leap years). - 5
Build a monthly dashboard
Assemble a compact dashboard by combining the month labels, totals, and a simple chart to visualize monthly trends.
Tip: Keep the dashboard lightweight; pivot-like summaries via QUERY scale well with data size.
Prerequisites
Required
- Required
- Required
- Sufficient data in date and numeric columns for examplesRequired
Optional
- Optional
- Locale awareness for date parsingOptional
- Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cells | Ctrl+C |
| PastePaste to destination | Ctrl+V |
| Fill downFill formula or value down one row | Ctrl+D |
FAQ
What is the difference between MONTH and TEXT when dealing with months?
MONTH returns a numeric month value (1-12). TEXT formats a date into a string, which can be a full month name or a sortable label like '2026-02'. Choose based on whether you need numbers for arithmetic or strings for labels and grouping.
MONTH gives you a number, TEXT gives you a string. Use them together to label and aggregate by month.
How do I convert dates stored as text into real dates in Google Sheets?
Use DATEVALUE to convert text dates into serial date numbers. If needed, wrap with TO_DATE for compatibility. For errors, apply IFERROR to provide a fallback value.
Convert with DATEVALUE, and guard with IFERROR to handle invalid dates.
Can I group data by month without a pivot table?
Yes. Use a combination of TEXT to create a yyyy-mm label and a QUERY or SUMIF to aggregate by that label. This mimics a monthly pivot table in a formula-based approach.
You can group by month using TEXT for labels and QUERY or SUMIF for totals.
What about locale differences in date parsing?
Date parsing depends on locale settings. Prefer DATEVALUE for conversions and TEXT with explicit formats to ensure consistency across locales. When in doubt, test with representative date formats.
Locale can affect parsing; use DATEVALUE and explicit format strings.
Is this approach suitable for large datasets?
Formula-based month extraction scales reasonably, but for very large datasets consider QUERY-based aggregations or Apps Script automation to optimize performance and reduce repetitive calculations.
Yes, but for big data use QUERY or Apps Script to keep things fast.
The Essentials
- Extract numeric months with MONTH(A2)
- Get month names with TEXT(A2, 'mmmm')
- Create consistent year-month labels (yyyy-mm) for sorting
- Use QUERY/UNIQUE for monthly aggregations
- Leverage Apps Script for automated month formatting