(WEB_PORTAL) Calculating Work Hours on the Server and Exporting Them as Excel

Overview

So that the responsible department could aggregate monthly work hours per employee for payroll purposes, we added a feature that lets users specify a date range and immediately download an Excel file with the calculated work hours.

Why We Chose This Stack

• For Excel generation, we chose a library that can build the file on the server side and stream it directly, so the browser only needs to trigger a download with no conversion logic of its own.
• Since this feature involves a lot of date arithmetic, we also brought in a date utility library so we wouldn't have to implement period-calculation logic by hand.

Key Implementation Points

Work Hour Calculation Logic

We implemented a calculation flow on the server that computes actual work hours from clock-in/clock-out records, excluding any time that overlaps with unpaid break periods, and separately tallies any hours that exceed the standard work hours.

Time on premises = Clock-out time - Clock-in time
Break deduction = Overlap between time on premises and the unpaid break period
Actual work hours = Time on premises - Break deduction
Regular hours = min(Actual work hours, Standard work hours)
Overtime hours = max(0, Actual work hours - Standard work hours)

Configurable Parameters

Since the break time window and standard work hours can vary by organization and change over time, we avoided hardcoding them. Instead, they're entered by the admin, alongside the date range, and fed into the calculation.

A Two-Sheet Layout

Within a single Excel file, we split the content into a summary sheet and a daily-detail sheet, so the person handling payroll could check both the overall totals and the individual daily records.

Retrospective

• For logic like time calculations, where an error translates directly into a payroll discrepancy, we confirmed that keeping the calculation parameters (break time, standard work hours, etc.) adjustable on screen — rather than fixed in code — makes it far easier to adapt to future policy changes.