Display Power Query Dates as Month and Year Without Converting to Text
Question details
Display a Power Query date as a month and year while retaining a date value for sorting and calculations.

- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user wants dates to appear as labels such as Jan 2022 without losing their date properties.
- Observed behavior
- A month-year label has no day component, but a true date needs a complete date value. Converting the value to text can affect date calculations and chronological sorting.
Check that the source column has the Date data type in Power Query. Decide whether you need a formatted date in an Excel worksheet or a separate text label in the query output.
Format the Loaded Date Column in Excel
Use a custom number format to show the month and year while keeping each cell as a true date.
In Power Query Editor, select the date column and choose Transform > Data Type > Date.
Choose Home > Close & Load to load the date column into an Excel worksheet.
Select the loaded date cells, open Format Cells, and choose Number > Custom. Enter mmm yyyy in the Type box and select OK.

Create a Separate Month-Year Label in Power Query
Use a text label when you need month-year values in the query output, while retaining the original date for sorting and calculations.
Display Month and Year in WPS Spreadsheets
Open a workbook containing date values in WPS Spreadsheets and apply a custom format to display the month and year without converting the dates to text. This formats worksheet cells; it does not change an Excel Power Query.
- 1. Open the workbook: Open WPS Spreadsheets and use Open to select the workbook containing the dates.
- 2. Select the dates: Select the cells or column containing the date values.
- 3. Set the format: Open Format Cells, choose Number > Custom, and enter mmm yyyy as the format.
- 4. Apply and save: Select OK to apply the format, then use File > Save or Save As to save the workbook.

Frequently Asked Questions
Can a true date contain only a month and year?
A date value includes a day. You can hide the day in Excel with Format Cells > Number > Custom and the format mmm yyyy, but the underlying date remains complete.
Will a formatted date still sort chronologically?
Yes. When you sort the formatted date column, Excel uses the underlying dates, including their day values, rather than the displayed month-year labels.
How do I sort month-year text labels in date order?
Keep the original date column and sort that column in Power Query or in the loaded Excel table. Sorting the text labels themselves may not produce chronological order.
Can I use the month-year text label in date calculations?
Use the original date column for date calculations. A label created with Date.ToText is text, even if it looks like a date.




