---
name: pto-spreadsheet-layout
description: Explains the structure and layout of PTO Excel spreadsheets used in the DWP Hours Tracker data migration process.
title: pto-spreadsheet-layout
canonical_url: https://skilld.dev/gh/corey-alix/dwp-hours/pto-spreadsheet-layout
last_updated: 2026-10-07T01:30:59.000Z
---

> Index of every page on this site: [llms.txt](https://skilld.dev/llms.txt)

> **Skill from skilld.dev.** Follow the user's requested action.
> If the user asked to fork this Skill, follow the fork workflow below. Do not execute the Skill instructions while copying it.
> Otherwise, follow the instructions below for this session. You do not need to install anything.
>
> If the user asked to install this Skill, run `npx skilld install corey-alix/dwp-hours/pto-spreadsheet-layout`. Install writes the Skill files into the project, so every session loads them.
>
> ## Fork workflow
>
> A fork creates an editable local Skill with its original author and licence. The request authorizes copying and local installation.
> 1. Check `./skills/pto-spreadsheet-layout`, the project lockfile, and selected Agent targets together. If the local directory or installed Skill exists, stop. Never overwrite an existing directory or Agent target.
> 2. Read [source metadata](https://skilld.dev/api/v1/skills/corey-alix/dwp-hours/pto-spreadsheet-layout) once. Use sourceUrl, sourceCommit, skillPath, sourceGone, and license. If the source is gone or its path is missing, stop. If license is null, read licence files at the source commit.
> 3. Fetch only the source commit into a temporary Git repository. Do not clone full history. Derive repository_url from sourceUrl, including repository renames. If sourceCommit is absent, resolve the sourceUrl ref once. Set source_commit to that actual commit. Run these commands in one shell call:
>
> ```sh
> git init --quiet "$temporary_dir"
> git -C "$temporary_dir" fetch --quiet --depth=1 "$repository_url" "$source_commit"
> git -C "$temporary_dir" checkout --quiet --detach FETCH_HEAD
> ```
>
> Read applicable licence declarations and notices at that commit. If copying is not permitted, report the restriction and stop.
> 4. Inspect source entries together, then copy the directory containing skillPath into `./skills/pto-spreadsheet-layout`. Use the user's path if selected. Keep the original SKILL.md, relative links, scripts, binary assets, and executable modes. Exclude .git metadata. Reject symlinks and paths outside the Skill directory. After checking entries, use cp -a where available. A regular source directory needs no custom copy script. Do not save this page wrapper as SKILL.md.
> Preserve author credit, notices, and applicable licence files from repository or parent directories. Add PROVENANCE.md with the Skill page, source URL, actual commit, original path, and licence. Retain any existing PROVENANCE.md and record new provenance separately. Batch source inspection, copying, and provenance work where practical.
> 5. In the project root, run `skilld install ./skills/pto-spreadsheet-layout --mode copy --plain`. If skilld is unavailable, use `npx skilld install ./skills/pto-spreadsheet-layout --mode copy --plain`. This known command needs no help lookup. Install does not support --json. Use detected Agent targets, or add --agent for the targets the user selected. Install the local path, never the upstream selector. If installation fails, preserve the local copy and report the exact failure.
> 6. Confirm the local lockfile source and installed Agent copies once. Report the local path, actual commit, and Agent targets. After edits, reinstall the same local path. Upstream updates must not replace it. Do not publish or push unless the user asks.

# PTO Spreadsheet Layout

## Description

Explains the structure and layout of PTO Excel spreadsheets used in the DWP Hours Tracker data migration process, including calendar areas, legend sections, PTO calculation data, and color coding schemes.

## Trigger

Activate when users ask about Excel spreadsheet structure, PTO data layout, calendar organization, legend interpretation, or how to navigate the legacy Excel files for data migration.

## Response Pattern

1. **Identify Key Sections**: Locate and describe the main areas of the spreadsheet (calendar, legend, PTO calculations)
2. **Explain Calendar Layout**: Detail the calendar area coordinates, date formulas, and cell structure
3. **Describe Legend Section**: Explain how PTO types are mapped to colors in the legend
4. **Cover PTO Calculations**: Describe the PTO calculation section and data format
5. **Explain Color Coding**: Detail how colors represent different PTO types and how to extract them
6. **Provide Navigation Tips**: Give guidance on programmatically accessing different sections

## Examples

- "How is the PTO Excel spreadsheet organized?"
- "Where is the calendar in the Excel file?"
- "How do I find the legend colors?"
- "What's the layout of the PTO calculation section?"
- "How are the dates structured in the calendar?"

## Additional Context

This skill integrates with the data-migration task and provides foundational knowledge for the ExcelJS-based migration implementation. It helps developers understand the source data structure before implementing extraction logic.

## Precise Spreadsheet Layout

### Legend Location

- **Coordinates**: Row 8, Columns 26–27 (cells Z8:AA8), spanning 2 columns
- **Structure**: Vertical list starting with "Legend" header (merged Z–AA), followed by colored cells for each PTO type
- **PTO Types and Colors** (from sample "Corey Alix 2025.xlsx"):
  - Sick: FF00B050 (green)
  - Full PTO: FFFFFF00 (yellow)
  - Partial PTO: FFFFC000 (orange-yellow)
  - Planned PTO: FF00B0F0 (blue)
  - Bereavement: FFBFBFBF (gray)
  - Jury Duty: FFFF0000 (red)

### Calendar Layout

- **Coordinates**: B6:X37 (rows 6-37, columns 2-24)
- **Month Headers**: Located in row 4, with each month spanning multiple consecutive columns
- **Precise Month Locations** (from sample "Corey Alix 2025.xlsx"):
  - January: Row 4, Columns 4-6 (D4-F4)
  - February: Row 13, Columns 4-6 (D13-F13) - continued from row 4
  - March: Row 22, Columns 4-6 (D22-F22) - continued from row 4
  - April: Row 31, Columns 4-6 (D31-F31) - continued from row 4
  - May: Row 4, Columns 12-14 (L4-N4)
  - June: Row 13, Columns 12-14 (L13-N13) - continued from row 4
  - July: Row 22, Columns 12-14 (L22-N22) - continued from row 4
  - August: Row 31, Columns 12-14 (L31-N31) - continued from row 4
  - September: Row 4, Columns 20-22 (T4-V4)
  - October: Row 13, Columns 20-22 (T13-V13) - continued from row 4
  - November: Row 22, Columns 20-22 (T22-V22) - continued from row 4
  - December: Row 31, Columns 20-22 (T31-V31) - continued from row 4
- **Date Structure**: Each month's header spans 3 merged columns (center of a 7-column block), but the full calendar block is 7 columns wide (Sun–Sat), with dates flowing vertically (one row per week)
- **Cell Content**: Numbers 1-31 representing days, calculated using Excel array formulas with PTOYEAR variable

### Programmatic Cell Lookup (Month → Cell)

Given a 1-based month number `M` (1=Jan, 12=Dec) and a day-of-month `D`, compute the Excel cell:

```
colGroup  = floor((M - 1) / 4)        // 0, 1, or 2
rowGroup  = (M - 1) % 4               // 0, 1, 2, or 3
headerRow = 4 + rowGroup * 9           // 4, 13, 22, or 31
startCol  = 2 + colGroup * 8           // 2 (B), 10 (J), or 18 (R)
dataRow   = headerRow + 2              // first row of day numbers

firstDow  = dayOfWeek(year, M, 1)      // 0=Sun, 6=Sat
week      = floor((D - 1 + firstDow) / 7)
dow       = (D - 1 + firstDow) % 7    // 0=Sun..6=Sat

row = dataRow + week
col = startCol + dow
```

**Quick reference table** (headerRow / startCol):

| Month     | M   | colGroup | rowGroup | headerRow | startCol | Cols |
| --------- | --- | -------- | -------- | --------- | -------- | ---- |
| January   | 1   | 0        | 0        | 4         | 2 (B)    | B–H  |
| February  | 2   | 0        | 1        | 13        | 2 (B)    | B–H  |
| March     | 3   | 0        | 2        | 22        | 2 (B)    | B–H  |
| April     | 4   | 0        | 3        | 31        | 2 (B)    | B–H  |
| May       | 5   | 1        | 0        | 4         | 10 (J)   | J–P  |
| June      | 6   | 1        | 1        | 13        | 10 (J)   | J–P  |
| July      | 7   | 1        | 2        | 22        | 10 (J)   | J–P  |
| August    | 8   | 1        | 3        | 31        | 10 (J)   | J–P  |
| September | 9   | 2        | 0        | 4         | 18 (R)   | R–X  |
| October   | 10  | 2        | 1        | 13        | 18 (R)   | R–X  |
| November  | 11  | 2        | 2        | 22        | 18 (R)   | R–X  |
| December  | 12  | 2        | 3        | 31        | 18 (R)   | R–X  |

**Verified example** — December 19, 2018 (Wednesday):

- `colGroup=2, rowGroup=3, headerRow=31, startCol=18 (R)`
- Dec 1, 2018 = Saturday → `firstDow=6`
- `week = floor((19-1+6)/7) = 3`, `dow = (18+6)%7 = 3` (Wed)
- **row=36, col=21 (U)** → cell **U36** ✓ (confirmed: value=19, fill=FFFFC000, note="PTO at 1PM")

**Day-of-week column order** within each 7-column block:

| Offset | 0   | 1   | 2   | 3   | 4   | 5   | 6   |
| ------ | --- | --- | --- | --- | --- | --- | --- |
| Day    | Sun | Mon | Tue | Wed | Thu | Fri | Sat |

### PTO Calculation Section

- **Location**: Starts at row 39 (after "PTO CALCULATION SECTION" header)
- **Data Structure**: Monthly breakdown with columns for work days, daily rate, accrued hours, carryover, used hours, and remaining balance
- **Month Range**: January through December (12 months of PTO data)
- **Data Extraction**: Skip 2 header rows after section title, then parse monthly data rows

### Detailed PTO Calculation Data Section

- **Location**: D42-W53 (rows 42-53, columns 4-23)
- **Headers**: Located in rows 40-41 (two-row header structure)
- **Column Structure**:
  - **D-E (Work Days in Month)**: Number of work days in each month
  - **F-G (Daily Rate)**: Daily PTO accrual rate (e.g., 0.68, 0.71 hours per day)
  - **J (Accrued PTO)**: Accrued PTO hours (single column)
  - **L-M (Previous Month's Carryover)**: PTO hours carried over from previous month. **Cell L42 (January row) specifically contains the prior year's carryover hours** — this is the value used by `parseCarryoverHours()` in `shared/excelParsing.ts` to set `employee.carryover_hours` during import. For subsequent months (rows 43–53), columns L-M contain the running balance carried forward from the previous month within the same year.
  - **O-P (Subtotal PTO hours)**: Subtotal of accrued + carryover hours. Formula: `J42+(IF(L42="", 0, L42))` for the January row.
  - **S-T (PTO hours per Month)**: PTO hours used/taken during the month
  - **V-W (Total Available PTO)**: Final available PTO balance after usage
- **Data Pattern**: Each row represents one month (January through December)
- **Calculation Logic**: Shows the detailed breakdown of PTO accrual, carryover, and usage calculations
- **Relationship**: This detailed section corresponds to the summary data in the main PTO calculation section above

### Acknowledgement Sections

- **Employee Acknowledgements**: Column X (24), Rows 42-53
  - **Purpose**: Records employee acknowledgement/approval for each month's PTO data
  - **Sample Data**: "CA" (Corey Alix, appears in all 12 rows)
  - **Structure**: One acknowledgement per month, corresponding to months in column B
- **Admin Acknowledgements**: Column Y (25), Rows 42-53
  - **Purpose**: Records admin approval/acknowledgement for each month's PTO data
  - **Sample Data**: "Mandi" (appears in all 12 rows)
  - **Structure**: One acknowledgement per month, corresponding to months in column B
- **Relationship**: Both acknowledgement columns align with the monthly data in the PTO calculation section

### Employee Information Section

- **Employee Name**: Cell J2 (Row 2, Column 10)
  - **Format**: Bold, 14pt employee full name
  - **Purpose**: Identifies the employee for the worksheet
- **Hire Date**: Cell R2 (Row 2, Column 18)
  - **Format**: "Hire Date: <date>"
  - **Sample Data**: "Hire Date: 2/13/23"
  - **Purpose**: Records the employee's hire date for HR and payroll purposes

### Sick Hours Tracking Section

- **Location**: Rows 32-34, Columns Y-AB (25-28)
- **Structure**:
  - **Row 32**: "Sick Hours Allowed" label merged Y-Z-AA (25-27), value in AB (28)
  - **Row 33**: "Sick Hours Used" label merged Y-Z-AA (25-27), value in AB (28)
  - **Row 34**: "Sick Hours Remaining" label merged Y-Z-AA (25-27), value in AB (28)
- **Purpose**: Tracks annual sick time allowance vs usage alongside the calendar grid
- **Sick Hours Allowed**: 24 hours per year (from SICK_HOURS_BEFORE_PTO business rule)
- **Sick Hours Used**: Sum of all PTO entries with type "Sick" for the year
- **Sick Hours Remaining**: Allowed minus used

### Color Coding System

- **Direct Cell Fills**: Colors are applied directly to calendar cells (no conditional formatting rules found)
- **Actual Usage in Sample**: Only yellow (FFFF00/FFFFFF00) is used for PTO days (24 cells total)
- **Color Extraction**: Use ExcelJS `cell.fill.fgColor.argb` or `cell.fill.fgColor.rgb` properties
- **Color Matching**: Normalize colors by removing alpha channel (FF prefix) for comparison

### Month Layout Pattern

- **Column Spanning**: Each month header appears in row 4 and spans exactly 3 consecutive columns
- **Row Continuation**: Month headers continue in rows 13, 22, and 31 for subsequent weeks of the same month
- **Date Flow**: Within each month's 3-column block, dates flow vertically (7 rows per week)
- **Year Coverage**: Full calendar year from January to December
- **PTO Distribution in Sample**:
  - January: 1 PTO day
  - May: 4 PTO days
  - September: 4 PTO days
  - Other months: Varies by employee schedule

### Calendar Cell Notes (Comments)

- **Format**: Excel cell notes (comments) with rich text, typically containing the author name in bold followed by a newline and the note content
- **Structure**: `{texts: [{font: {bold: true, ...}, text: "Author Name:"}, {font: {...}, text: "\nNote content"}]}`
- **Common usage**: Partial PTO cells (`FFFFC000` fill) often have notes specifying actual hours taken when less than a full 8-hour day
- **Examples from "A Bylenga" sheet in `reports/2018.xlsx`**:
  - M27 (July 25): `"Mandi Davenport:\n2 HRS PTO"` — 2 hours PTO
  - N27 (July 26): `"Mandi Davenport:\n5 HRS PTO"` — 5 hours PTO
  - O27 (July 27): `"Mandi Davenport:\n5 HRS PTO"` — 5 hours PTO
- **Hours pattern**: Note text typically matches `/^(\d+(?:\.\d+)?)\s*(?:hrs?|hours?)\s+(?:pto|sick|bereavement)/i`
- **Access via ExcelJS**: Use `cell.note` property; for rich text notes, concatenate `cell.note.texts[].text` to get the full display string
- **Known bug**: The importer currently ignores notes on color-matched cells, defaulting to 8h per entry instead of reading the actual hours from the note. See `TASKS/legacy-data-import.md` Known Issues.

### Known Layout Anomalies

- **Row offset in legacy spreadsheets**: Some employee sheets have extra blank rows inserted within a single month's calendar area, shifting the date array formula down by 1 (or more) rows. This is a per-sheet, per-month anomaly — other months on the same sheet and other employee sheets are unaffected.
- **Discovered instance**: In `reports/2018.xlsx`, the "Deanna Allen" sheet has an extra blank row in the July area (colGroup=1, rowGroup=2). The date array formula starts at J25 instead of the expected J24. All other months on the same sheet (and all months on other sheets like "A Bylenga") use the standard `headerRow + 2` offset.
- **Impact on parsing**: Without detection, the parser reads offset cells — 5 of 6 colored PTO cells are missed (they fall beyond the day loop's reach), and the one detected cell is mapped to the wrong date. For Deanna Allen's July, this produced 8h detected vs 44h declared.
- **Detection algorithm**: Before iterating each month's days, verify that cell `(headerRow + 2, startCol + firstDow)` contains the numeric value `1` (day 1 of the month). If not, scan ±3 rows at the same column. If day 1 is found at an offset, log a recovery warning and use the corrected start row. If day 1 cannot be located, skip the month with an error warning.</content>
  <parameter name="filePath">/home/ca0v/code/ca0v/dwp-hours/.github/skills/pto-spreadsheet-layout/SKILL.md
