Kition
sheetBusiness Templates

Weekly Timesheet

Clock-in and clock-out times turned into daily hours, daily overtime past eight hours and the week's pay, with time arithmetic.

Weekly Timesheet — Sheet sample rendered by Kition

Times are typed as clock times and stored as fractions of a day, so a shift is end minus start, less the lunch break, times 24; MOD keeps a shift that crosses midnight positive and ROUND keeps quarter hours exact. Anything over eight hours in a day is overtime at time and a half. The dates follow from the week-start cell, TEXT names each day, the weekend rows are shaded, and the signature block is ready for printing.

Steps

51 commands
showcase.yaml82 lines
steps:
  - command: range.setValues
    params:
      range: A1
      values:
        - [Weekly Timesheet]
        - [Employee, Jordan Ellis, null, Week starting, "2027-06-14"]
        - [Role, Warehouse lead, null, Hourly rate, 24]
  - command: range.setValues
    params:
      range: A5
      values:
        - [Day, Date, Start, Lunch out, Lunch in, Finish, Hours, Regular, Overtime]
        - ["=TEXT(B6,\"dddd\")", "=$E$2", "08:00", "12:00", "12:30", "16:45", "=IF(C6=\"\",0,ROUND(MOD(F6-C6,1)*24-(E6-D6)*24,2))", "=MIN(G6,8)", "=G6-H6"]
        - ["=TEXT(B7,\"dddd\")", "=B6+1", "07:45", "12:00", "12:45", "17:15"]
        - [null, null, "08:00", "12:15", "12:45", "16:30"]
        - [null, null, "08:30", "12:30", "13:00", "18:00"]
        - [null, null, "08:00", "12:00", "12:30", "15:30"]
        - [null, null, "09:00", null, null, "13:00"]
        - [null, null, null, null, null, null]
        - [Total, null, null, null, null, null, "=SUM(G6:G12)", "=SUM(H6:H12)", "=SUM(I6:I12)"]
  - { command: edit.fillDown, params: { range: "A7:B12" } }
  - { command: edit.fillDown, params: { range: "G6:I12" } }
  - command: range.setValues
    params:
      range: F15
      values:
        - [Regular pay, null, "=H13", "=H15*$E$3"]
        - [Overtime pay (1.5×), null, "=I13", "=H16*$E$3*1.5"]
        - [Total pay, null, null, "=I15+I16"]
  - command: range.setValues
    params:
      range: A15
      values:
        - [Employee signature]
        - [null]
        - [Supervisor approval]
  - { command: home.fontSize, params: { range: A1, size: 20 } }
  - { command: home.bold, params: { range: A1 } }
  - { command: home.fontColor, params: { range: A1, color: "#155e75" } }
  - { command: home.fontColor, params: { range: "A2:A3", color: "#6b7280" } }
  - { command: home.fontColor, params: { range: "D2:D3", color: "#6b7280" } }
  - { command: home.bold, params: { range: "B2:B3" } }
  - { command: home.bold, params: { range: "E2:E3" } }
  - { command: home.fontColor, params: { range: "E2:E3", color: "#1d4ed8" } }
  - { command: home.numberFormat, params: { range: "E2", format: "d mmm yyyy" } }
  - { command: home.numberFormat, params: { range: "E3", format: "$$0.00" } }
  - { command: home.alignLeft, params: { range: "E2:E3" } }
  - { command: home.fillColor, params: { range: "A5:I5", color: "#155e75" } }
  - { command: home.fontColor, params: { range: "A5:I5", color: "#ffffff" } }
  - { command: home.bold, params: { range: "A5:I5" } }
  - { command: home.alignCenter, params: { range: "B5:I5" } }
  - { command: home.numberFormat, params: { range: "B6:B12", format: "d mmm" } }
  - { command: home.numberFormat, params: { range: "C6:F12", format: "h:mm AM/PM" } }
  - { command: home.alignCenter, params: { range: "B6:F12" } }
  - { command: home.fontColor, params: { range: "C6:F12", color: "#1d4ed8" } }
  - { command: home.numberFormat, params: { range: "G6:I13", format: "0.00;;\"\"" } }
  - { command: home.alignCenter, params: { range: "G6:I13" } }
  - { command: home.bold, params: { range: "G6:G13" } }
  - { command: home.borders, params: { range: "A6:I12", preset: insideHorizontal, color: "#e5e7eb" } }
  - { command: home.conditionalFormat, params: { range: "A6:I12", rule: { type: expression, formula: "=WEEKDAY($B6,2)>5", style: { fill: { fg: { Rgb: 14873076 }, pattern: Solid } } } } }
  - { command: home.conditionalFormat, params: { range: "I6:I12", rule: { type: expression, formula: "=I6>0", preset: yellowFillDarkYellowText } } }
  - { command: home.bold, params: { range: "A13:I13" } }
  - { command: home.fillColor, params: { range: "A13:I13", color: "#ecfeff" } }
  - { command: home.borders, params: { range: "A13:I13", preset: topDoubleBottom } }
  - { command: home.numberFormat, params: { range: "H15:H16", format: "0.00 \"h\"" } }
  - { command: home.numberFormat, params: { range: "I15:I17", format: "$$#,##0.00" } }
  - { command: home.fontColor, params: { range: "F15:H16", color: "#4b5563" } }
  - { command: home.bold, params: { range: "F17:I17" } }
  - { command: home.fillColor, params: { range: "F17:I17", color: "#155e75" } }
  - { command: home.fontColor, params: { range: "F17:I17", color: "#ffffff" } }
  - { command: home.borders, params: { range: "B15:D15", preset: bottom, color: "#6b7280" } }
  - { command: home.borders, params: { range: "B17:D17", preset: bottom, color: "#6b7280" } }
  - { command: home.fontColor, params: { range: "A15:A17", color: "#6b7280" } }
  - { command: home.italic, params: { range: "A15:A17" } }
  - { command: home.columnWidth, params: { cols: "A:A", width: 118 } }
  - { command: home.columnWidth, params: { cols: "B:B", width: 92 } }
  - { command: home.columnWidth, params: { cols: "C:F", width: 74 } }
  - { command: home.columnWidth, params: { cols: "G:I", width: 66 } }
  - { command: sheet.read, params: { range: "A6:B12" }, as: days }
  - { command: sheet.read, params: { range: "G6:I13" }, as: hours }
  - { command: sheet.read, params: { range: "I15:I17" }, as: pay }

Checks

5 checks on every build
  • results.days.values = [["Monday",46552],["Tuesday",46553],["Wednesday",46554],["Thursday",46555],["Friday",46556],["Saturday",46557],["Sunday",46558]]
  • results.hours.values = [[8.25,8,0.25],[8.75,8,0.75],[8,8,0],[9,8,1],[7,7,0],[4,4,0],[0,0,0],[45,43,2]]
  • results.pay.values = [[1032],[72],[1104]]
  • Diagnostics: none, the kit's own check finds nothing wrong
  • The saved document opens again, and its thumbnail matches thumb.png within the render tolerance

Related

More Sheet samples
sheetBusiness Templates

Payroll Register

A fortnight's payroll for a small bistro, from hours and rates through overtime, pre-tax retirement, withholding and FICA to net pay.

1 sheet; used range A1:L14
sheetBusiness Templates

Renovation Estimate

A contractor's bathroom quote with labour and materials, optional extras switched by a Yes/No list, markup, contingency, tax and deposit.

1 sheet; used range A1:F26
sheetBusiness Templates

Travel Expense Report

A conference trip's receipts in two currencies, converted, split into company-paid and reimbursable, and summed by category.

1 sheet; used range A1:L16
sheetBusiness Templates

Print Shop Invoice

A ready-to-send invoice with line discounts, rounded sales tax, a deposit and the balance due, laid out without gridlines.

1 sheet; used range A1:F24