A month-end balance chart that starts on January 31, 2026 should put its April tick on April 30. Advance each tick from the one before it with calendar months and the labels come out as 2026-01-31, 2026-02-28, 2026-03-28, 2026-04-28, with every later tick stuck on the 28th. Advance by a flat 30 days instead and the February tick lands on March 2, while the December tick lands on December 27. The rule that survives both traps is simple to state: compute every tick from the axis minimum, and clamp month ends to the real calendar
On a quarter-close chart the first mistake is the dangerous one, because the picture still looks right. The line rises where it should, the labels are evenly spaced, and the March balance sits three days before quarter end. A controller comparing the chart with the ledger is the person who finds it, and by then the chart has usually been pasted into a board pack
Why do month ticks drift when they are accumulated?
Month ticks drift because clamping throws information away. Once January 31 has been clamped to February 28, the fact that the series wanted the 31st is gone, and any step taken from the clamped value inherits the loss. In HotXLS, TXLSChartDateAxisTransform.BuildTicks in the lxChart unit never steps from a previous tick: tick i is always AddUnits(MinValue, UnitKind, Step * i, ...), so March is two months after January 31 and comes out as March 31. AddUnits adds the count straight to the serial for day units; for month and year units it converts the serial to a real date, calls IncMonth or IncYear, and converts back
The loop is bounded on every side. The result array never exceeds 4096 entries whatever the caller passes as the maximum, Step * i is checked against MaxInt before the multiplication happens, and generation stops as soon as a tick fails to increase or passes the axis maximum. A hostile workbook with a span of a few million years produces a short array and returns
The transform is public, so you can run the arithmetic yourself before any chart exists. This console snippet builds a year of month ticks for the series above and prints the labels the chart will use
uses
SysUtils, lxChart;
var
Ticks: TXLSChartDateValues;
MinSerial, MaxSerial: Double;
I, Count: Integer;
begin
// 1900 date system (Dates1904 = False), month-end anchored series
MinSerial := TXLSChartDateAxisTransform.DateTimeToSerial(
EncodeDate(2026, 1, 31), False);
MaxSerial := TXLSChartDateAxisTransform.DateTimeToSerial(
EncodeDate(2026, 12, 31), False);
// One-month step, aim for 12 ticks, never more than 64
Count := TXLSChartDateAxisTransform.BuildTicks(MinSerial, MaxSerial,
1, xcduMonths, False, 12, 64, Ticks);
for I := 0 to Count - 1 do
Writeln(TXLSChartDateAxisTransform.FormatValue(Ticks[I], False));
// 2026-01-31, 2026-02-28, 2026-03-31, 2026-04-30 ... 2026-12-31
end;There is exactly one approximation in BuildTicks, and it sits where an approximation is harmless. When the requested step is zero, the method estimates a step by treating a month as 30 days and a year as 365, then rounds the raw step up to a 1, 2 or 5 multiple of a power of ten. That estimate decides only how many units lie between ticks. Positions still come from IncMonth and IncYear. A rough month length can make the axis a little denser or sparser, but every tick still lands on a date that one of those two functions produced
Workbook serials stay the axis coordinate
A HotXLS date axis keeps workbook serials as its coordinate space because Delphi's TDateTime has no slot for serial 60. In the 1900 date system that serial is February 29, 1900, a day that never existed and that Excel keeps for compatibility with older spreadsheet programs. TXLSChartDateAxisTransform.TrySerialToDateTime handles it the only way a real calendar can: serials below 60 shift by one day and serial 60 folds onto February 28. For labeling a single point that is fine. For geometry it is wrong, since serials 59 and 60 become the same TDateTime, and February 28 and March 1 end up one unit apart where Excel draws them two apart
Three serials show both sides of the problem. FormatValue special-cases the phantom slot and prints exactly what Excel prints, while a round trip through TDateTime loses it
var
S: Integer;
D: TDateTime;
begin
for S := 59 to 61 do
begin
TXLSChartDateAxisTransform.TrySerialToDateTime(S, False, D);
Writeln(S, ' axis label: ',
TXLSChartDateAxisTransform.FormatValue(S, False),
' TDateTime: ', FormatDateTime('yyyy-mm-dd', D));
end;
// 59 axis label: 1900-02-28 TDateTime: 1900-02-28
// 60 axis label: 1900-02-29 TDateTime: 1900-02-28
// 61 axis label: 1900-03-01 TDateTime: 1900-03-01
end;Hardly anyone charts data from early 1900, so the practical lesson is about your own code. If you compute axis bounds or annotation positions, do it in serials and convert with DateTimeToSerial at the edge, the way the first snippet does. Mix a serial-based axis with TDateTime arithmetic in the caller, and an annotation dated before March 1, 1900 lands one day away from its point. The second epoch matters more often: in a 1904 workbook serial 0 is January 1, 1904, and the transform applies the fixed 1462-day offset in both directions when the Dates1904 argument is true. Both epochs, and how cell number formats interact with them, are covered in our article on Excel date serials, the 1904 system and number formats
How does HotXLS choose days, months or years automatically?
TXLSChartDateAxisTransform.DetectUnit picks the finest calendar unit that the gaps in the data actually need. It drops NaN and infinite values, sorts a copy of the rest once, and skips duplicate dates. If any adjacent gap is shorter than one calendar month, the unit is days; otherwise, if any gap is shorter than a calendar year, months; otherwise years. The month test is IncMonth(Previous, 1) > Current, and that choice matters for exactly the data in this article. A 30-day threshold would call the gap from January 31 to February 28 "less than a month" and switch a monthly series to days. With fewer than two distinct valid dates there is no gap to measure, and the method returns days
ResolveUnits merges the detected unit with whatever the chart declares. An explicit base unit wins. A missing major or minor unit inherits the detected unit when that is coarser than the base, and otherwise falls back to the base. The last rule is the one people miss: if the effective major or minor unit is finer than the base, the base drops to match. The base unit decides how points are grouped before any tick exists, because Normalize snaps a value to its day, the first of its month or January 1. A monthly base under a daily major unit would fold a whole month of points into one slot before the tick code ever saw them
How many automatic major ticks appear depends on more than the date range. The plot length, the measured width of the axis font, the label number format and the projected width of rotated labels all cap the count, so a narrow chart with long mmmm yyyy labels gets fewer ticks than a wide one with mm/yy. Labels, tick marks and gridlines are all drawn from one tick array, which keeps HTML, SVG and PDF output in agreement, and a minor tick that falls on a major tick is drawn once
What does the dateAx element store, and what does omission mean?
In ChartML every calendar setting on a date axis is optional, and leaving one out says something different from writing its default. The CT_DateAx type in ECMA-376 Part 1, §21.2 (DrawingML Charts) lets baseTimeUnit, majorTimeUnit, minorTimeUnit and auto each appear or not. An omitted baseTimeUnit tells Excel to decide when it renders; an explicit days pins it. TXLSXChartAxis keeps each value beside a presence flag: BaseTimeUnit with BaseTimeUnitSet, MajorTimeUnit with MajorTimeUnitSet, MinorTimeUnit with MinorTimeUnitSet, and AutoDateAxis with AutoDateAxisSet. Assigning a value sets its flag, setting the flag to False returns the element to omission, and the XLSX writer emits an element only when its flag is on. auto is easy to misread: it controls automatic category-versus-date detection and has nothing to do with automatic tick units
uses
SysUtils, DateUtils, lxHandleX;
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Chart: TXLSXChart;
Axis: TXLSXChartAxis;
M: Integer;
begin
Book := TXLSXWorkbook.Create;
try
Sheet := Book.Sheets.Add('Close');
Sheet.Cells[1, 1].Value := 'Month end';
Sheet.Cells[1, 2].Value := 'Balance';
for M := 1 to 12 do
begin
Sheet.Cells[M + 1, 1].Value :=
EncodeDate(2026, M, DaysInAMonth(2026, M));
Sheet.Cells[M + 1, 2].Value := 1000 + M * 75;
end;
Chart := Sheet.AddLineChart('Month-end balance',
'Close!$A$2:$A$13', 'Close!$B$2:$B$13', 15, 1, 32, 9);
Axis := Chart.CategoryAxis;
Axis.Kind := xlsxAxisDate; // writes c:dateAx
Axis.BaseTimeUnit := xlsxChartTimeDays; // also sets BaseTimeUnitSet
Axis.MajorTimeUnit := xlsxChartTimeMonths;
Axis.MajorUnit := 1; // a value > 0 marks majorUnit as set
Axis.NumberFormat := 'mmm yyyy';
Axis.NumberFormatSourceLinked := False;
Axis.TextStyle.Rotation := -45; // degrees
// AutoDateAxis left untouched: no c:auto element is written
Book.SaveAs('month-end-balance.xlsx');
finally
Book.Free;
end;
end;The base unit here is days on purpose. Month-end dates fall on different day numbers, and a monthly base would snap every point to the first of its month, which moves the March 31 balance to March 1. Pin the base to days whenever the exact day carries meaning, and let the major unit do the monthly spacing
Labels and orientation follow the same presence rules. The axis numFmt element defaults sourceLinked to true, so TXLSXChartAxis keeps the format code, the source-linked value and NumberFormatSet apart, and an empty code is never mistaken for a missing element. ChartML stores text rotation in 1/60000 of a degree with positive values running clockwise; TextStyle.Rotation takes plain degrees, and a label set to -45 leans the same way in SVG and PDF output. A reversed (maxMin) axis mirrors major and minor ticks, gridlines, data points, trendlines and error bars together. Reading works the other way around: open a workbook written elsewhere and the flags tell you what its author actually specified
var
Book: TXLSXWorkbook;
Axis: TXLSXChartAxis;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('forecast.xlsx') <> 1 then
raise Exception.Create('Cannot open workbook');
Axis := Book.Sheets[1].Charts[0].CategoryAxis; // Sheets[] is 1-based, Charts[] is 0-based
if Axis.Kind = xlsxAxisDate then
begin
if Axis.BaseTimeUnitSet then
Writeln('baseTimeUnit = ', Ord(Axis.BaseTimeUnit))
else
Writeln('baseTimeUnit omitted: Excel chooses at render time');
if Axis.AutoDateAxisSet then
Writeln('auto = ', Axis.AutoDateAxis);
end;
finally
Book.Free; // save it again and the omitted elements stay omitted
end;
end;Calendar units in ODS and legacy XLS files
The calendar model reaches as far as each file format can express it. ODF 1.2 describes axis spacing with chart:interval-major, a plain number with no notion of days, months or years, so an Excel monthly axis saved as ODS cannot carry its unit. HotXLS leaves the unit out of the ODS file rather than invent a non-standard attribute that other ODF applications would ignore anyway. If the monthly spacing matters to the people opening the file, ship XLSX
BIFF8 workbooks keep date axis settings in the AxcExt record, nine fixed 16-bit words. Its automatic flags mask fields without removing them, so a record marked "automatic minimum" still carries a minimum value. HotXLS keeps the masked minimum, maximum, interval, unit and crossing values on read and writes them back unchanged, keeps unknown unit codes intact in the XLS object model, and maps only the valid date units 0, 1 and 2 (days, months, years) when a chart is converted to XLSX
A date axis rarely travels alone. When an Excel workbook pairs a date-axis line with a column series on a secondary axis, our article on preserved ChartML and combination charts explains how HotXLS replays the original chart XML when nothing changed and merges typed edits into it when something did, and our overview of charts, images and drawings covers the anchoring and series calls used above. The HotXLS Delphi Component page lists the full chart feature set for Delphi and C++Builder, including XLS and XLSX chart reading, writing and rendering without Excel installed