Check Overnight Timesheet Hours in OpenOffice Calc

An overnight interval needs an end date, not just an end time. Build a fictional activity log that calculates net elapsed hours, rejects a reversed interval and keeps 30 hours 30 minutes from displaying as 6:30.

This is a time-arithmetic exercise. It does not calculate wages, overtime, statutory breaks or payroll/legal compliance.

Scope and units

The intended application is free Apache OpenOffice Calc 4.1 desktop, with English functions and semicolon separators, using Linux-style menus. The linked official 4.1 guide and Calc reference document the date serials, functions and elapsed-hours display used here. This new fixture has not been executed in OpenOffice; no exact installed build, native screenshot or save/reopen result is claimed.

Use timestamps in one consistent time basis and at whole-minute precision. Spreadsheet date serials do not carry a timezone or automatically resolve daylight-saving changes. For intervals crossing a clock change, obtain verified elapsed time or normalized timestamps first. Do not silently apply this example to ambiguous local times.

Enter full start and end timestamps

Start a blank workbook. Put these headers in A1:H1: Activity, Start, End, Break minutes, Gross minutes, Input status, Net duration, Decimal hours.

Enter the formulas below in B:C. Format B2:C5 through Format → Cells → Numbers with a date-and-time format such as YYYY-MM-DD HH:MM and widen both columns.

Row	A: Activity	B: Start formula	C: End formula	D: Break minutes
2	Evening setup	=DATE(2026;11;9)+TIME(22;0;0)	=DATE(2026;11;10)+TIME(6;0;0)	30
3	Day preparation	=DATE(2026;11;10)+TIME(9;0;0)	=DATE(2026;11;10)+TIME(17;30;0)	30
4	Overnight observation	=DATE(2026;11;11)+TIME(21;30;0)	=DATE(2026;11;12)+TIME(7;0;0)	30
5	Pack-down	=DATE(2026;11;12)+TIME(10;0;0)	=DATE(2026;11;12)+TIME(16;0;0)	0

Breaks are minutes as plain numbers: 30 means half an hour. Do not enter 0.30, 00:30 or 30% in D. Enter explicit zero for no break; leave a missing measurement blank so the checker can reject it.

Calculate duration only after checking inputs

In E2 enter and copy through E5:

=IF(COUNT(B2:C2)<>2;"";ROUND((C2-B2)*1440;0))

Dates are day counts, so multiplying their difference by 1,440 converts it to minutes. Rounding to an integer removes tiny numeric residue for this whole-minute dataset. It is not a payroll-rounding policy and must not silently discard meaningful seconds from other data.

In F2 enter and copy through F5:

=IF(COUNT(B2:D2)<>3;"CHECK INPUT";IF(C2<=B2;"CHECK ORDER";IF(OR(D2<0;D2<>ROUND(D2;0);D2>E2);"CHECK BREAK";"OK")))

In G2 enter and copy through G5:

=IF(F2="OK";(E2-D2)/1440;"")

In H2 enter and copy through H5:

=IF(F2="OK";G2*24;"")

The status requires numeric timestamps and break minutes, a strictly later end, and a whole nonnegative break no longer than the interval. It does not verify the real-world timestamp. A break equal to the interval is allowed and yields zero net time; equal start and end are rejected as an ambiguous or empty record.

Compare clock display with elapsed duration

Format G2:G7 using Format → Cells → Numbers and enter [HH]:MM in the Format Code field. Brackets let accumulated hours exceed 24. Format E and D as ordinary numbers, and H as ordinary numbers with two decimal places.

Row	Expected gross minutes E	Status F	Net duration G	Decimal hours H
2	480	OK	07:30	7.50
3	510	OK	08:00	8.00
4	570	OK	09:00	9.00
5	360	OK	06:00	6.00

Put Total net time in F7, =IF(COUNT(G2:G5)<>4;"CHECK ROWS";SUM(G2:G5)) in G7, and =IF(COUNT(H2:H5)<>4;"CHECK ROWS";SUM(H2:H5)) in H7.

Expected G7: 30:30. Expected H7: 30.50. Independently, gross minutes total 1,920, breaks total 90, and net minutes are 1,830; 1,830 ÷ 60 = 30.5 hours. A plain HH:MM clock display can wrap that to 06:30 without changing the stored value. Decimal 7.50 means seven and a half hours, not seven hours fifty minutes.

Rehearse errors instead of hiding them

Save a checkpoint. Try each change separately and restore it before continuing:

• Change C2 to =DATE(2026;11;9)+TIME(6;0;0). The end precedes the start: F2 must say CHECK ORDER, G2 and H2 display empty, and both totals say CHECK ROWS. Do not repair this with MOD or an automatic extra day; confirm the end date.
• Clear D3. A missing break is not a known zero: expect CHECK INPUT and blocked totals.
• Set D4 to 600. It exceeds 570 gross minutes: expect CHECK BREAK.
• Set D3 to 510. A full-interval break gives zero net time; totals become 22:30 and 22.50.
• Extend C5 to =DATE(2026;11;12)+TIME(16;15;0). Totals become 30:45 and 30.75.

Restore the original records and verify 30:30 again. For a verified interval longer than 24 hours, retain both dates: subtraction preserves whole days. Whether such a record is plausible requires review; the formula does not impose a work-policy maximum.

Save and troubleshoot

Save a new OpenDocument Spreadsheet (.ods) file. Reopen it and repeat the 15-minute extension yourself. CSV cannot preserve this workbook’s formulas and duration formats as a reusable model.

If CHECK INPUT appears, inspect the cells: date-like text is not a numeric timestamp. If a formula is displayed, consult formula text instead of results. If the total looks like 1.270833, it is shown in days; apply the elapsed-duration format. See custom numeric display formats for stored values versus display.

Adding records requires extending every formula, both total ranges and the required row count of four. Preserve an original copy and record the reason for timestamp corrections.

Sources and evidence limits

Official sources: Apache OpenOffice 4.1 Calc Guide, date/time serial arithmetic, elapsed time above 24 hours, COUNT, and ROUND. Original expected results were independently checked using datetime differences. Native formula evaluation, displayed formatting and file persistence remain unverified for this fixture.