Inside clndr_data: decoding a P6 calendar

Almost everything in an XER file is a flat table column, and Oracle's XER Import/Export Data Map Guide names it. One column is not flat. CALENDAR.clndr_data is a single cell holding a small bracketed tree — the working week, the shift times, and every holiday, shutdown and make-up day the calendar declares — packed into one tab-delimited field.

It is also the column with no downstream symptom. Get the calendar wrong and every date, every float and every delay attribution moves together, and the schedule still looks entirely reasonable. A reader that silently falls back to Monday-to-Friday when it cannot parse the blob loses a two-week December shutdown and finishes the project two weeks early with nothing in its output to look at.

This page documents the grammar as this project's importer reads it, with a worked decode. If you are writing or auditing schedule tooling, check it against a real export; the structures below were derived from real exports and from several P6 versions that disagree with each other.

The overall shape

A blob is a tree of balanced parentheses. Groups carry a name, an attribute group, and a member group. The two named groups that matter are:

• DaysOfWeek — the recurring weekly pattern.
• Exceptions — dated departures from it.

Both are usually wrapped in an outer CalendarData group, though not every writer emits that wrapper.

There is no separator between sibling blocks, so a fixed-width read or a find on the next marker will run into the following day's shifts. The only reliable way through the blob is a bracket walk that tracks nesting depth, taken from the group's own opening bracket rather than from the position of its name — slicing at the text Exceptions cuts the tree mid-branch, and a bracket walk over an unbalanced fragment returns the wrong subtree instead of failing.

Days of the week

A day block is (N(attrs)(shifts)), where N runs Sunday = 1 to Saturday = 7. Real P6 exports prefix the block with a serial and two pipes:

(0||2()(0||1(s|08:00|f|16:00)))

That is P6 day 2 — Monday — with one shift from 08:00 to 16:00. The 0|| prefix is present in every real export and absent from most hand-written fixtures, so a parser must accept both forms. Requiring the unprefixed form is a real defect with a specific signature: every genuine calendar silently falls back to a default week.

A day is a working day if it lists at least one shift. A day block with an empty shift group is a non-working day. There is no explicit working flag.

Note what makes the day markers unsearchable: a day is written (0||2()(…)) and a shift inside it is written (0||1(s|13:00|f|17:00)()), structurally the same shape. Any string search for "day number 1" finds the second shift of Monday first. Walking the tree is the only way to tell a day from a shift.

Shifts

A shift is a labelled pair of clock times:

s|08:00|f|16:00

s is the start and f the finish, and the two are not always written in that order. At least one P6 version writes f|16:00|s|08:00 throughout a file. Matching on position rather than on the labels reads every shift in such a file as no shift at all — and the failure is quiet, because the weekly pattern only needs to see an s| to call the day worked, so the days still come out right while every hours figure reads zero and every check that compares hours silently reports nothing.

A pair carrying the same marker twice is not a shift and should be skipped rather than guessed at.

A finish at or before the start is an overnight shift, not a zero-length one: P6 writes night work that way, and the finish belongs to the next calendar day. A day may carry several shifts, and the gap between them is unpaid — a day of 08:00–12:00 and 13:00–17:00 is an eight-hour day, not a nine-hour one, and that unpaid hour is exactly the difference between a remaining-duration timestamp landing at 16:49 and at 15:49.

Exceptions

Exceptions live under Exceptions as blocks carrying a day serial:

(0||0(d|41818)((0||0(s|09:00|f|15:00)())))

The distinction that matters is shifts or no shifts.

Reading only the first bracket group after d|<serial> finds the empty attribute group, sees no shift, and turns every make-up day in the file into a shutdown — silently, and in the direction that makes the schedule look later than the contractor planned it.

Two nestings occur in the wild, and a parser has to accept both. P6 itself writes the serial inside the attribute group, (0||0(d|41818)(shifts)); other writers put it where the block's name goes, (0||d|41818()(shifts)). The same doubling applies to the day blocks: members may hang one level below the named group, (0||DaysOfWeek()(<days>)), or directly off it. Committing to one shape reads the other as an empty week, which is a fallback to a default week with nothing logged.

The day serial and its epoch

d|41818 is a day serial counting from 30 December 1899 — the same epoch spreadsheets use, including the leap-year quirk that puts the origin on the 30th rather than the 31st. So:

• serial 41818 is 28 June 2014, a Saturday;
• serial 46016 is 25 December 2025.

Nothing in the blob states the epoch, and an off-by-one or off-by-two epoch produces holidays that are real, plausible and on the wrong days. A serial that cannot be converted — out of range, or not a number — is a dropped day, and dropping it silently loses a shutdown; this importer reports it as XER.CALENDAR.BAD_EXCEPTION_SERIAL.

A worked decode

Take this blob, a ten-hour Sunday-to-Thursday week with four exceptions:

(0||CalendarData()(DaysOfWeek()
(1()(0||1(s|07:00|f|17:00)))(2()(0||1(s|07:00|f|17:00)))
(3()(0||1(s|07:00|f|17:00)))(4()(0||1(s|07:00|f|17:00)))
(5()(0||1(s|07:00|f|17:00)))(6()())(7()(0||1(s|07:00|f|17:00)))
)(Exceptions()
(0||0(d|41818)((0||0(s|09:00|f|15:00)())))
(0||1(d|41819)((0||0(s|07:00|f|17:00)())))
(0||2(d|41820)())
(0||3(d|41821)((0||0(f|14:30|s|09:30)())))
)))

Reading it out, group by group. The weekly pattern lists seven day blocks. Days 1, 2, 3, 4, 5 and 7 each carry one 07:00–17:00 shift, so Sunday, Monday, Tuesday, Wednesday, Thursday and Saturday are worked at ten hours. Day 6 — Friday — has an empty shift group and is the week's day off.

The four exceptions decode as follows.

Running that blob through this project's importer returns exactly those four, with the shutdown flagged non-working and the reversed pair read at five hours — and it emits one disclosure: two exception days work less than a full ten-hour day and are counted as whole working days, overstating work on them by nine hours. This engine's work pattern is day-granular, so a six-hour Saturday is worked as a whole day. The arithmetic is not corrected; it is stated, because the alternative is to be wrong in silence.

Why two tools disagree about the same calendar

Every disagreement below has been observed between readers of the same file, and each one is silent in at least one direction.

That last one deserves its own sentence. A P6 project calendar is derived from a global one and stores its own departures; the shared shutdown calendar can live entirely in the base record, linked by base_clndr_id. Reading only the child's own blob works days the project does not — and because the link is a field P6 stored rather than a figure it computed, following it is a decoding fix rather than borrowing P6's answer. A base chain can be several deep, and a cycle in it has to stop at the repeat.

Empty, unreadable, and contradictory

Three states are not parse results but findings, and each is reported by name rather than absorbed:

• clndr_data is empty. Reported as XER.CALENDAR.NO_DATA. A default week is assumed and every holiday on that calendar is lost.
• The blob has no DaysOfWeek section. Reported as XER.CALENDAR.UNPARSEABLE — a truncated blob, a column limit, or a shape this reader does not know. Same consequence.
• Fewer than seven day blocks parsed, or no day carried any shift. Reported as XER.CALENDAR.PARTIAL_WEEK and XER.CALENDAR.NO_WORKING_DAYS. A calendar nobody ever works is far rarer than a blob somebody misread, so the second is raised as an error.

Separately, the blob can contradict its own row. CALENDAR.day_hr_cnt is the divisor every duration in the file converts through, and a calendar can declare eight while its shifts describe ten. Both figures reach this importer and the disagreement is reported as XER.CALENDAR.HOURS_DISAGREE either way. Where the shifts run longer than the declared day, the shifts are used — the stored dates in such files are consistent with the shifts. Where the shifts read shorter, the declared figure is kept, because every way of misreading a week biases the shift figure low and never high, so a short reading is as likely a misparse as a real short week.

See also What an XER file is, How it works, and What this does not do.

Source: web/pages/xer-calendars.md. Source commit date: 2026-09-11.