How to Create an Excel Chart Showing Months and Years Chronologically
Question details
The user needs to correctly extract months and years from a date column and create a chart that displays the data chronologically without returning incorrect years or repeating month names.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Extracting specific month and year values from standard dates to build a time-based chart.
- Observed behavior
- Using date functions combined with incorrect cell formatting returns wrong years (such as 1905) or repeats 'January' for every row instead of displaying the actual chronological months.
Verify that your original date column contains true Excel date values rather than text strings, as functions like YEAR and MONTH will only work correctly on recognized dates.
Extract Dates with TEXT Functions and Sort Data Manually
Use the TEXT function to reliably extract formatted months and years, sort the raw data chronologically, and generate the chart from the ordered dataset.
When Excel charts sort months alphabetically rather than chronologically, it usually means the chart is referencing text labels instead of underlying date values. To fix this, you must sort the source data by actual date values before charting.
In an empty column next to your dates, type =YEAR(A5) to extract the year. Crucially, format this new cell as 'General' or 'Number'. Do not format it as 'yyyy', or Excel will convert the result to the year 1905.
In another column, type =TEXT(A5,"mmm") to get the short month name, or =TEXT(A5,"mmm yyyy") to get a combined label like 'Jan 2023'.
Highlight your entire dataset. Navigate to Data > Sort in the ribbon. Choose to sort by your original date column (Column A) and set the order from Oldest to Newest.
Select your newly extracted chronological labels and your data values. Go to the Insert tab and select your preferred chart type. The chart will now display the timeline chronologically.
Use a PivotTable and PivotChart to Group Dates
Use Excel's built-in PivotTable features to automatically group underlying dates by months and years chronologically without needing extra formula columns.
Create Chronological Charts Effortlessly with WPS Spreadsheet
WPS Spreadsheet offers powerful date handling and charting features, making it simple to extract dates and build chronologically sorted charts. It seamlessly handles complex date formulas and automated PivotTables.
- 1. Open your data file: Launch WPS Spreadsheet and open the document containing your original date column.
- 2. Create chronological labels: Use the formula =TEXT(A2,"mmm yyyy") in an adjacent column to easily generate accurate month and year text labels.
- 3. Sort the dataset: Highlight your data table, navigate to the Data tab, and click Sort to arrange your rows by the original date column chronologically.
- 4. Select your data points: Highlight the newly created label column alongside the numerical data you wish to chart.
- 5. Insert your chart: Go to the Insert tab, click Chart, and choose a line or column chart. The X-axis will automatically reflect your chronological timeline.
Frequently Asked Questions
Why does the =YEAR() function return 1905?
If you format a cell containing the =YEAR() function as a custom date (like 'yyyy'), Excel treats the extracted year number (e.g., 2023) as a serial date. The 2023rd day in Excel's calendar falls in the year 1905. To fix this, change the cell's format to 'General' or 'Number'.
Why does =TEXT(MONTH(A5),"mmm") return January for every row?
The MONTH function simply returns an integer between 1 and 12. If you pass that integer into the TEXT function and format it as 'mmm', Excel translates the numbers 1 through 12 as the first twelve days of January 1900. To get the correct month name, reference the date cell directly by using =TEXT(A5,"mmm").
How do I stop my Excel chart from sorting months alphabetically?
Charts sort text labels alphabetically if they are not tethered to underlying date values. To correct this, you must physically sort your source data by the actual date column (from oldest to newest) before inserting the chart, or rely on a PivotChart which groups dates chronologically by default.
