How to Fix Dates on an Excel Chart Axis

The quick answer: when an Excel chart shows the wrong dates, it’s almost always one of two things. Either your dates are stored as text (Excel is plotting them as meaningless labels or falling back to 1900-era serial numbers), or Excel has silently switched your axis to a date axis and is regularizing your dates to the first of each month. The first problem is fixed by converting the text to real dates. The second is fixed in Format Axis > Axis Type, where you choose Text axis to show exactly the dates in your sheet.

That covers most cases. But “wrong dates” means different things to different people, so diagnose your symptom first, then jump to the matching fix.

Your symptom	The fix you need
Axis shows 1/1/1900, Jan-00, or dates from decades ago	Fix 1 — your dates are text
Your 6/30 month-end dates display as 6/1	Fix 2 — force a text axis (or Fix 3 to keep a date axis)
Chart shows every date between your data points, with gaps	Fix 3 — change the axis type or base unit
Scatter chart won’t offer any date options at all	Fix 4 — scatter charts have no date axis
Axis shows 1, 2, 3… or the wrong column entirely	Fix 5 — reassign the axis labels
Dates are correct but formatted wrong (e.g., 45123 or mm/dd when you want mmm-yy)	Fix 6 — change the axis number format

Why Excel changes your dates in the first place

This behavior looks like a bug. It isn’t — it’s a feature working exactly as designed, on data you didn’t expect it to trigger on.

When you build a chart, Excel inspects the first column of your source data. If it sees valid dates, it automatically applies a date axis (Microsoft calls it a time-scale axis). A date axis doesn’t plot your dates as labels — it plots them as points on a continuous timeline, spaced proportionally, and it picks a base unit (days, months, or years) by scanning for the smallest gap between any two dates in your data.

That’s genuinely useful when your dates are irregular. It’s infuriating when you entered clean month-end dates and Excel decides to label the axis with the first of each month instead, because the base unit is “months” and months start on the 1st.

If Excel sees text instead of dates — which happens constantly with data exported from databases, accounting systems, and CRMs — you get the opposite failure: a text axis treating each date as an arbitrary label, or worse, values plotted against serial numbers that render as 1900-era dates.

Everything below follows from those two mechanisms.

Fix 1: Your dates are stored as text (the most common cause)

If your axis shows 1/1/1900, Jan-00, or a range of ancient dates, your “dates” aren’t dates. They’re text strings that look like dates. Formatting the cells as Date doesn’t fix this — formatting changes how a value displays, not what it is. A text string stays a text string no matter what number format you slap on it.

The tell: text dates align left in their cells by default; real dates align right.

The fastest conversion — Text to Columns:

1. Select the column of text dates.
2. Go to Data > Text to Columns.
3. Click Next twice, and in step 3 choose Date, then pick the order that matches your data (MDY for US-style dates).
4. Click Finish. The values convert to true dates in place, and the chart repairs itself immediately.

The formula alternative: if you’d rather not overwrite the source, add a helper column with =VALUE(A2) (or =DATEVALUE(A2) for date-only strings), format it as a date, and point the chart’s axis at the helper column instead.

One warning: if VALUE returns a #VALUE! error, your strings contain something non-date — trailing spaces, non-breaking spaces from a web export, or a day/month order that doesn’t match your regional settings. Strip those with =TRIM(CLEAN(A2)) first.

Fix 2: Excel shows 6/1 when your data says 6/30 — force a text axis

This is the classic complaint: your table clearly says June 30, but the axis says June 1. Excel’s automatic date axis has kicked in with a base unit of months, and month ticks land on the first of the month.

If you just want the axis to display exactly the dates in your sheet, in the order they appear:

1. Right-click the horizontal axis and choose Format Axis.
2. Under Axis Options > Axis Type, select Text axis.

Done. The axis now shows your literal dates. The trade-off — and it’s a real one — is that a text axis spaces every point evenly, regardless of the actual time between them. If your data is monthly, that’s fine. If your dates are irregular (a reading on Jan 3, then Jan 5, then Mar 20), a text axis visually lies about the gaps. In that case, keep the date axis and use Fix 3 instead.

Fix 3: Keep the date axis, but take control of the base unit

The date axis is worth keeping when time spacing matters — trend lines over irregular intervals, for instance. You just need to stop Excel guessing the base unit:

1. Right-click the axis > Format Axis.
2. Set Axis Type to Date axis explicitly (don’t leave it on Automatic).
3. Under Units, set Base to Days, Months, or Years yourself.
4. Set Major units to control how often labels appear — e.g., Base: Days with Major: 7 days gives you weekly labels on daily data.

This also solves the reverse problem, where Excel fills in every calendar date between your data points and your chart is mostly empty space: switch Base from Days to Months, or use a text axis if the fill-in dates shouldn’t exist at all.

While you’re in this pane, resist the urge to also hand-tune the bounds unless you have a reason — Excel’s automatic min and max are usually right, and manually fixed bounds are a leading cause of the next mystery chart problem. If your axis scale (not the dates) is what’s wrong, that’s a separate repair covered in our guide to fixing the Excel chart axis scale (/fix/excel-chart-axis-scale).

Fix 4: Scatter charts don’t have a date axis — and never will

If you’ve been hunting for the “Date axis” option on an XY scatter chart, stop: it isn’t there. A scatter chart’s horizontal axis is a plain value axis. It will happily plot dates as their underlying serial numbers, but it offers no base units and no date regularization.

Two ways out:

• Switch the chart type to Line. A line chart supports a true date axis with all the controls from Fix 3. For most time-series data, this is the right answer anyway — the position we’d defend is that if your X values are dates, you wanted a line chart, not a scatter.
• Stay with scatter and format the numbers. If you need scatter behavior (multiple readings per date, true proportional spacing), keep the chart and set the axis Number format to a date code like m/d/yyyy. The serial numbers will display as dates. You lose base-unit controls but keep proportional spacing.

Fix 5: The axis is plotting the wrong thing entirely

If your axis shows 1, 2, 3… or repeats a value from your data table instead of the dates, Excel never assigned your date column as the axis labels — usually because the chart was built from a partial selection.

1. Click the chart, then go to Chart Design > Select Data.
2. Under Horizontal (Category) Axis Labels, click Edit.
3. Clear the range box and select your date cells (data only — don’t include the header).
4. Click OK twice.

The dates snap into place. If they come in as 1900-era dates after this, you’ve got Fix 1’s problem underneath: the newly assigned labels are text.

Fix 6: The dates are right but look wrong

Sometimes nothing is broken except the formatting — the axis shows 45123 instead of Jul-23, or full dates where you wanted month abbreviations. Right-click the axis > Format Axis > Number, and either pick a Date category or enter a custom code: mmm-yy for Jan-26, d mmm for 15 Jan, yyyy for years only.

Uncheck Linked to source if you want the axis format to differ from the worksheet’s — otherwise your custom code gets overridden by the cell formatting.

Download the working file

Reading fixes is one thing; seeing them side by side is faster. The free workbook below contains a Broken sheet reproducing the two big failures — text dates rendering as 1900 gibberish, and month-end dates reverting to the 1st — and a Fixed sheet showing the same charts repaired, with notes on exactly what changed.

Download Date Axis Fix

Free .xlsx file

Download

Open both sheets, click each axis, and compare the Format Axis settings. That comparison teaches the mechanism better than any tutorial.

---

Related guides: now that the axis behaves, make sure the chart itself is the right one — our decision guide on which chart you should use (/choose/which-chart-should-you-use) settles that by data type. If the scale rather than the dates is misbehaving, see how to take back control of the Excel chart axis scale (/fix/excel-chart-axis-scale). And if you’re charting financial periods, the waterfall chart tutorial (/excel/waterfall-chart-excel) shows the modern native method most sites still don’t teach.