Set a Date-Based Y-Axis Range in an Excel Line Chart
Question details
Set a meaningful vertical date range in an Excel line chart when dates from different years create unsuitable Y-axis bounds.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- The user wants to chart month-and-day values on the Y-axis without the original years affecting the axis scale.
- Observed behavior
- Excel treats dates as serial numbers, so dates from widely different years can cause the Y-axis to start or end at unexpected dates.
Make sure the Y-axis source cells contain valid Excel dates rather than text. Choose one reference year that can represent every month-and-day value you want to display.
Create helper dates with one common reference year
Use this method when the chart should compare only the month and day, not the original year stored in each date.
Excel stores each date as a serial number. Rebuilding every Y-axis date with the same year keeps the chart scale within one calendar year while preserving the month and day.
Insert a new column next to the original Y-axis date values and name it something such as Chart Date.
In the first helper cell, enter =DATE(2024,MONTH(B2),DAY(B2)), replacing B2 with the cell that contains the original date.
Drag the fill handle or copy the formula down so every source date has a corresponding helper date in the same reference year.
Select the chart, then go to Chart Design > Select Data and edit the series so the Y values reference the helper-date column.
Right-click the Y-axis, choose Format Axis, open Number, select Custom, and enter d mmm to display labels such as 1 Mar and 5 Nov.

Set minimum and maximum bounds for the date Y-axis
Use this after creating common-year helper dates when you need the vertical axis to show a specific date interval.
Create a Controlled Date Y-Axis with WPS Spreadsheets
WPS Spreadsheets lets you create helper dates, update chart series, and control axis bounds so a line chart can display month-and-day values without original years distorting the scale.
- 1. Open the worksheet in WPS Spreadsheets: Open the Excel workbook in WPS Office and select the sheet containing the line chart and source dates.
- 2. Create a common-year helper column: In an empty column, enter =DATE(2024,MONTH(B2),DAY(B2)) and fill the formula down for all charted dates.
- 3. Update the chart data series: Click the chart and use Chart Tools > Select Data to replace the Y-axis source range with the helper-date column.
- 4. Set axis bounds and label format: Right-click the Y-axis, choose Format Axis, set the required Bounds, then apply the custom number format d mmm.

Frequently Asked Questions
Why does my Excel chart Y-axis start at 1900 when my data contains modern dates?
Excel stores dates as serial numbers. If your Y-axis includes dates from very different years, automatic scaling can include an early serial-date range. Create helper dates using one common year before charting.
Can I show only the month and day on an Excel chart axis?
Yes. Right-click the chart axis, select Format Axis, open Number, choose Custom, and enter d mmm. This displays labels such as 1 Mar without showing the year.
How do I set a fixed date range on a chart Y-axis?
Right-click the Y-axis and choose Format Axis. Under Axis Options > Bounds, enter the Minimum and Maximum using the same reference year as your helper dates.
Do helper dates change my original date data?
No. Helper dates are separate formula values in another column. Your original dates remain unchanged, while the chart uses the normalized dates for its Y-axis.




