Convert a monthly attendance sheet into LOP days for payroll

The muster and its codes

Most offices keep attendance as one row per employee and one column per day, with a code in each cell. Agree the code list in writing before anything else; this article uses:

If your attendance comes from a biometric device, build this muster from the daily table described in Turn biometric punch logs into daily attendance with SQLite, then let approved leave requests overwrite the relevant days.

The Excel version

For October, day columns run from C (day 1) to AG (day 31). In the LOP column:

=COUNTIF(C2:AG2,"A")+COUNTIF(C2:AG2,"LWP")+0.5*COUNTIF(C2:AG2,"HD")

COUNTIF is not case-sensitive, so a lowercase "a" still counts. But a cell containing "AB", "Abs" or "A " with a trailing space is counted as nothing at all. That is the silent failure: the employee is simply not docked. Add a check column next to it:

=COUNTA(C2:AG2)-SUMPRODUCT(COUNTIF(C2:AG2,{"P","A","HD","PL","LWP","WO","H"}))

It returns the number of filled cells that are not one of the valid codes. Any non-zero value must be fixed before payroll runs. A data validation list on the day columns prevents most of these typos at entry; the check column catches the ones pasted in from elsewhere.

The same logic in pandas, with a sandwich rule

Excel handles simple counts well. A sandwich rule, where a weekly off or holiday between two unpaid days is itself unpaid, is awkward with formulas and easy in Python. Treat this rule as a policy option: many companies do not have one, and those that do define it differently.

import calendar
import pandas as pd

YEAR, MONTH = 2026, 10                    # October 2026 has 31 days
days = calendar.monthrange(YEAR, MONTH)[1]

# Synthetic muster: one row per employee, one column per day of the month.
# Codes: P present, A absent, HD half day, PL paid leave, LWP leave without pay,
#        WO weekly off, H holiday. Sundays in Oct 2026: 4, 11, 18, 25.
def month_row(overrides):
    row = {}
    for d in range(1, days + 1):
        row[d] = "WO" if calendar.weekday(YEAR, MONTH, d) == calendar.SUNDAY else "P"
    row.update(overrides)
    return row

muster = pd.DataFrame.from_dict({
    "E201": month_row({3: "A", 17: "HD", 22: "PL"}),
    "E202": month_row({3: "A", 5: "A", 23: "LWP", 26: "LWP"}),   # Sunday 4 sits between two absences
    "E203": month_row({12: "p", 13: "AB"}),                      # typos a formula would miss
}, orient="index")

VALID = {"P", "A", "HD", "PL", "LWP", "WO", "H"}
bad = muster.stack()
bad = bad[~bad.isin(VALID)]
print("Invalid codes to fix before payroll:")
print(bad.to_string())

def sandwich_days(codes):
    """Weekly offs/holidays with an unpaid day on both sides (policy option)."""
    unpaid = {"A", "LWP"}
    seq = list(codes)
    count = 0
    for i, c in enumerate(seq):
        if c in {"WO", "H"}:
            left = next((x for x in reversed(seq[:i]) if x not in {"WO", "H"}), None)
            right = next((x for x in seq[i + 1:] if x not in {"WO", "H"}), None)
            count += left in unpaid and right in unpaid
    return count

ok = muster.drop(index=bad.index.get_level_values(0).unique())
lop = pd.DataFrame({
    "absent": ok.eq("A").sum(axis=1),
    "lwp": ok.eq("LWP").sum(axis=1),
    "half_days": ok.eq("HD").sum(axis=1),
})
lop["sandwich"] = ok.apply(sandwich_days, axis=1)
lop["lop_days"] = lop["absent"] + lop["lwp"] + 0.5 * lop["half_days"] + lop["sandwich"]

GROSS = 36000   # hypothetical monthly gross; replace with each employee's figure
for divisor_name, divisor in [("calendar days", days), ("fixed 30", 30), ("fixed 26", 26)]:
    lop[f"deduction_{divisor_name.replace(' ', '_')}"] = (GROSS / divisor * lop["lop_days"]).round(2)
print(lop.to_string())

The script first lists invalid codes (E203 has "p" and "AB") and holds that employee back, rather than calculating on bad data. For E202, Sunday 4 October sits between absences on the 3rd and 5th, so it adds one sandwich day, taking LOP to 5. The LWP days on Friday 23rd and Monday 26th do not sandwich Sunday 25th, because Saturday 24th was a working day in between.

Note that the script treats lowercase "p" as invalid even though Excel's COUNTIF would accept it. Being strict is deliberate: if the entry process allows casual codes, other variations follow.

How much the divisor changes the deduction

Take a hypothetical monthly gross of ₹36,000 and E202's 5 LOP days in October:

The gap between the smallest and largest is over ₹1,100 for one employee in one month. None of these is "the correct formula" in general; the correct one is whatever your appointment letters, salary policy or applicable rules say. What you must never do is let the divisor drift from month to month because a different person ran the sheet. Put it in a labelled cell, reference that cell in every formula, and print it on the payroll summary.

Checks before handing over

• Every employee has exactly as many day cells as the month has days.
• Invalid-code count is zero for everyone included.
• LOP days never exceed the days in the month.
• Joiners and leavers are handled separately: days before joining or after the last working day should be a distinct code (for example "NJ" for not joined), not "A", or you will dock someone for days they were never employed.
• Total LOP days across the company is compared with last month. A large jump usually means a code changed, not that everyone skipped work.

Payroll-adjacent MIS work rewards exactly this discipline of explicit rules and pre-flight checks. Leave balances, the natural companion to LOP, are worked through in Calculate leave balances with monthly accrual and carry-forward cap, and building such checks into Excel and Python is regular practice in our Data Analyst programme.