How to convert date to month and year format in Google Sheets?

Converting a raw date format into a standardized month and year display is a fundamental requirement for effective data presentation and analysis within Google Sheets. While dates are often entered as full calendar values (e.g., 10/25/2023), statistical reporting frequently necessitates summarizing this data by monthly or yearly cohorts, requiring a simplified representation. Achieving this conversion can be done through two primary methodologies: visual cell formatting or using dedicated formula-based conversion, such as the powerful TEXT function. Understanding the difference between these approaches—one changing how the number appears, the other converting the underlying data type to a string—is crucial for selecting the correct tool for your spreadsheet workflow.

Understanding Date Formatting vs. Data Conversion

In Google Sheets, dates are stored internally as sequential serial numbers, where the number 1 corresponds to January 1, 1900. When you input a date like “October 25, 2023,” the application stores a large numerical value (e.g., 45223) and then applies formatting rules to display it in a recognizable calendar format. This distinction is vital: if you only apply visual formatting, the underlying cell value remains the serial number, which is useful for calculations (like finding the difference between two dates). Conversely, if you use a function like TEXT, the output becomes a text string (or string data type), which is fixed in its appearance but cannot be used reliably in subsequent mathematical date operations.

The choice between formatting and formula conversion depends entirely on the intended use of the resulting data. If your goal is simply to change the appearance of the original column while preserving its mathematical integrity, visual formatting is sufficient. However, if you need the month and year combination as a unique text key for grouping, sorting, or presentation in a separate column, or if you need to concatenate it with other text strings, then employing a formula that converts the date into a text string is the necessary approach. The TEXT function provides the ultimate control over the resulting output structure, allowing users to specify the exact combination of digits, abbreviations, and separators required for reporting.

Method 1: Applying Visual Formatting (Quick Display Change)

The most straightforward way to alter the display of an existing date column to show only the month and year is through the built-in formatting options. This method is quick and ideal when you do not need the resulting month/year combination to be in a separate, formula-driven column. To achieve this, you must first select the range of cells containing the dates you wish to modify. Once the appropriate cells are highlighted, right-click on the selection to access the contextual menu, and navigate to the ‘Format Cells’ option.

Within the formatting sidebar or dialog box, locate the ‘Number’ tab or section. Google Sheets offers a variety of standard and custom date formats. You should select the ‘Date’ category, which will typically present a list of predefined date structures. Look specifically for the option that represents the ‘Month and Year’ format, or use the ‘Custom date and time’ option to define your own pattern using codes like M and Y. Once the desired month-and-year display format has been chosen from the list, confirm the selection by clicking ‘OK’ or ‘Apply’ to instantly update the appearance of the selected cells without altering the underlying date serial number.

Method 2: Using the TEXT function (Data Type Conversion)

For scenarios requiring robust, permanent conversion of date values into specific month and year text strings—which is essential for creating dynamic reports or generating specific keys for pivot tables—the TEXT function is the preferred and most reliable tool. The TEXT function accepts a numerical value (in this case, the date serial number) and converts it into text according to a specified custom format code. This allows for precise customization that goes beyond the standard formatting options, enabling users to switch easily between numeric months, abbreviated names, or full names, and control the number of digits shown for the year.

The general structure, or syntax, for the TEXT function is always =TEXT(value, format_pattern). The value argument refers to the cell containing the original date you want to convert. The format_pattern argument is a string enclosed in double quotes that dictates how the output text should look, using specialized date code characters. Mastery of these format codes is key to generating the exact output needed. The following formulas illustrate the four primary ways to combine month and year using this powerful technique, catering to different reporting requirements, from numerical shorthand to full textual display.

You can use one of the following formulas to convert a date to a month and year in Google Sheets:

Formula 1: Convert Date to Month and Full Year (e.g. 01/2022)

=TEXT(A1, "mm/yyyy")

Formula 2: Convert Date to Month and Last Two Digits of Year (e.g. 01/22)

=TEXT(A1, "mm/yy")

Formula 3: Convert Date to Abbreviated Month and Full Year (e.g. Jan. 2022)

=TEXT(A1, "mmm. yyyy")

Formula 4: Convert Date to Full Month and Full Year (e.g. January 2022)

=TEXT(A1, "mmmm yyyy")

Note that each formula assumes the original date is stored in cell A1 and should be adjusted based on the specific cell reference used in your sheet.

Decoding the TEXT function Syntax

The core functionality of the TEXT function relies heavily on the use of specialized formatting codes which are case-sensitive and dictate the level of detail displayed for each component of the date. For the month and year conversion, we primarily focus on the M and Y codes. The number of times these letters are repeated determines whether the output is numerical, abbreviated, or spelled out fully. This precise control over the output formatting is what makes the TEXT function indispensable for consistent data preparation and reporting.

For the month component, using m or mm results in a numerical representation: a single m shows the month without a leading zero (e.g., 1 for January), while mm enforces a two-digit display with a leading zero (e.g., 01 for January). Moving up in complexity, mmm provides the standard three-letter abbreviation for the month (e.g., Jan, Feb, Mar), which is highly readable yet concise. Finally, mmmm forces the function to display the full, unabbreviated name of the month (e.g., January, February, March).

Similarly, the year component is governed by the y codes. Using yy limits the year display to only the last two digits (e.g., 22 for 2022), which is common in compact data displays or when space is limited. Conversely, using yyyy ensures that the full four-digit year is displayed (e.g., 2022), which is the standard practice for formal documentation and clear historical reference, mitigating potential ambiguity about the century. The separator character (e.g., a slash, a hyphen, or a space) placed between the month codes and the year codes inside the quotes is also entirely customizable, allowing users to match specific internal or external formatting standards.

Formula Breakdown: Four Essential TEXT function Variants

The four formulas presented above cover the vast majority of month-and-year formatting needs encountered in daily spreadsheet management. The first formula, =TEXT(A1, "mm/yyyy"), is perhaps the most universally accepted numerical representation, providing a clear two-digit month and the unambiguous four-digit year, separated by a forward slash. This format is excellent for systems that require numeric sorting or standardized input masks. The resulting output, such as 01/2022, is predictable and easily machine-readable.

The second variant, =TEXT(A1, "mm/yy"), is structurally identical but opts for brevity in the year component. By using only yy, we save space and prioritize conciseness, resulting in outputs like 01/22. While this saves visual space, it should be used cautiously in datasets spanning multiple centuries where the context of the century might be lost. The third formula, =TEXT(A1, "mmm. yyyy"), shifts from purely numerical output to text abbreviations. This provides a balance between readability and conciseness, yielding results like Jan. 2022. Note the inclusion of the period and the space separator within the format string.

Finally, the fourth and most verbose variant, =TEXT(A1, "mmmm yyyy"), is designed for maximum clarity and presentation quality. By utilizing four ‘M’s, we instruct the TEXT function to spell out the month completely. This is often preferred for presentation layers, dashboards, or reports intended for executive review, producing outputs such as January 2022. Although longer, this textual format eliminates any potential ambiguity associated with numerical month codes or abbreviations, ensuring clarity for the end user.

The following examples demonstrate the practical application and resulting output of each of the four formula variants discussed previously.

Example 1: Displaying Month and Full Year (mm/yyyy)

This first example demonstrates the application of the formula designed to produce a numerical month and a four-digit year, utilizing the mm/yyyy format pattern. This approach is highly recommended for creating standard chronological keys in a dataset. To initiate this process, assume your original dates are listed in Column A, starting from cell A2. We will place the conversion formula in cell B2 to generate the resulting month and year text string.

We can use the following formula to convert a date to a month and full year:

=TEXT(A2, "mm/yyyy")

After entering this formula into cell B2, the resulting output will instantly reflect the specified format (e.g., 01/2022). The crucial step for converting the entire dataset is to apply this formula down the column. This is efficiently achieved by selecting cell B2 and then dragging the fill handle (the small square at the bottom right corner of the cell) down to cover all corresponding rows in Column A. This action automatically adjusts the cell reference (from A2 to A3, A4, and so on), ensuring every date is correctly converted.

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Column B displays the numerical month and full four-digit year for each original date listed in column A, demonstrating clear and consistent formatting.

Example 2: Using Abbreviated Year Digits (mm/yy)

When space is a constraint or when working with large historical datasets where the century is implicitly understood, utilizing the two-digit year format is an excellent solution for compactness. This format is generated using the mm/yy pattern within the TEXT function. For this example, we once again reference the date contained in cell A2 and place the modified formula into cell B2.

We can use the following formula to convert a date to a month and the last two digits of the year:

=TEXT(A2, "mm/yy")

Upon entering the formula, the date will appear in the format of two-digit month followed by a two-digit year (e.g., 01/22). As with the previous example, the formula must be extended to the rest of the column using the fill handle. This allows for rapid population of Column B with the newly converted, abbreviated date strings. While visually concise, remember that the resulting data type is text, and the year ambiguity (e.g., is ’25’ 1925 or 2025?) is handled contextually by the user, not by the spreadsheet itself.

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Column B displays the numerical month and the last two digits of the year for each date in column A, offering a compact display.

Example 3: Converting to Abbreviated Month Name (mmm. yyyy)

If numerical output is less desirable than an abbreviated textual month, the mmm. yyyy format provides an excellent balance of readability and space-saving design. This format is particularly popular in reports and documentation where recognizing the month by name is important, but displaying the full name is too cumbersome. We utilize three ‘M’s in the format pattern to trigger the three-letter month abbreviation feature of the TEXT function.

We can use the following formula to convert a date to an abbreviated month name and the full year:

=TEXT(A2, "mmm. yyyy") 

Notice the inclusion of the decimal point (period) and the space immediately following the abbreviation code inside the quotation marks; this dictates the separation style in the output (e.g., Jan. 2022). Applying this formula to B2 and dragging it down ensures that all dates in Column A are converted into their corresponding three-letter month abbreviation coupled with the full four-digit year, significantly enhancing the visual clarity of the dataset summary compared to purely numerical formats.

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Column B displays the abbreviated month name and the full year for each date in column A, offering an easy-to-read textual summary.

Example 4: Generating Full Month Name and Year (mmmm yyyy)

The final and most detailed conversion option involves generating the full, spelled-out month name alongside the full year. This is achieved using four ‘M’s (mmmm) in the formatting string, typically separated from the year by a simple space. This format is highly desirable for final reports, publications, or user-facing interfaces where maximum clarity and professionalism are required, although it consumes the most horizontal space within the spreadsheet.

We can use the following formula to convert a date to a full month name and the full year:

=TEXT(A2, "mmmm yyyy") 

Once the formula is entered into cell B2, the resulting output will display the full name of the month followed by the year (e.g., January 2022). Consistent application across the entire column, achieved by dragging the formula down, ensures that the resulting summary column is consistently formatted, providing an unambiguous textual representation of the month and year for every entry in the original date column. This powerful conversion demonstrates the full flexibility provided by the TEXT function when handling complex date data type manipulations.

We can type this formula into cell B2 and drag the formula down to every remaining cell in column B:

Column B displays the full month name and full year for each date in column A, providing the most detailed and readable textual summary.

Conclusion: Selecting the Right Conversion Tool

Mastering the conversion of full date values into specific month and year formats is an essential skill for efficient data analysis in spreadsheet environments. As demonstrated, while standard cell formatting provides a quick visual solution, relying on the TEXT function offers superior flexibility, control, and consistency, converting the date serial number into a reliable text string that is ready for reporting and grouping. By carefully choosing the appropriate format pattern (e.g., mm/yyyy, mmm. yyyy, or mmmm yyyy), users can tailor their data outputs to meet any requirement, whether for compact data display or highly readable presentation materials.