Mistakes That Corrupt a Biweekly CSV

You export the last two weeks, open the CSV, and the total looks about right. It usually is not. The errors that inflate a biweekly total are small and quiet: one day counted twice, a stray decimal that reads as minutes, a shift someone forgot to close. None of them throw an error. They just push the number a few hours off, and you find out when a worker disputes their pay or a grant reviewer questions your hours. Here are the mistakes that do the most damage, ordered by how often they show up.

Counting the boundary day in both pay periods

This is the most common one. Your period runs Monday the 1st through Sunday the 14th. The next period starts Monday the 15th. Someone worked an overnight on the 14th that ended at 1 a.m. on the 15th. If you filter by date without deciding which side the shift belongs to, that shift lands in both exports, or in neither.

The fix is a single rule you write down and never change: a shift belongs to the pay period that contains its clock-in time. An entry that starts at 10 p.m. on the 14th is entirely in the first period, even though it ends after midnight. Apply that same rule every time and the boundary day stops being a coin flip. If you are unsure where a period should start and end, the definition of a biweekly pay period spells out the two-week cadence.

Mixing decimal hours and HH:MM in the same column

One row says 7.5. The next says 7:30. They look like the same amount of time, and they are, but a spreadsheet does not treat them the same way. Sum a column with both and you get garbage, because 7:30 may be read as a text string or as a fraction of a day. A worker who logged 8 hours 15 minutes gets recorded as 8.15 by someone who typed what they saw, and now that shift is short by 10 minutes.

Pick one format for the whole file and convert everything to it before you sum. Decimal is easier to add; 8.25 means eight and a quarter hours. If your rows are a mix, run them through a decimal and HH:MM converter so every cell reads the same way. The glossary entry on decimal hours explains why 15 minutes is 0.25 and not 0.15.

Forgotten clock-outs that run to the current time

Someone clocked in Tuesday and never clocked out. If your system leaves that entry open and closes it at the moment you export, that one shift can read as 40 or 90 hours. It is obvious when it is huge. It is dangerous when it is not: an entry that ran overnight and got closed at 9 a.m. looks plausible and inflates the total by a few hours nobody will catch.

Find every open entry before you export and resolve it with the worker. Better, use a system where a stale entry closes at its start time plus a fixed cutoff instead of the current time. The auto-close behavior means a forgotten clock-out never balloons into extra paid hours, because it is capped at a sane maximum rather than left running.

Editing the CSV by hand and breaking a column

You open the file, delete a row that looks like a duplicate, and shift every value one column to the left. Now the worker names line up with the wrong hours, and the total is still a number, so nothing warns you. Hand-editing also strips leading zeros from IDs and reformats dates into whatever your machine's locale prefers.

Do your filtering and correcting at the source, then export a fresh file. A CSV export with a stable column order that never changes means your payroll formulas keep pointing at the right cells every period, and you are not re-checking the layout each time.

Time zones that shift a shift to the wrong day

An entry stored in one zone and displayed in another can move across midnight. A 9 p.m. clock-in recorded in UTC shows as an earlier evening locally, or a later one, and a shift near the boundary jumps into the neighboring day. Suddenly your Sunday total is short and the next Monday is long.

Store times in UTC and display them in the viewer's local zone consistently, so the same shift always lands on the same calendar day for everyone looking at the report. What you want to avoid is one person exporting in their zone and another totaling in theirs.

Trusting the visible total instead of a distinct-worker count

Two rows for the same person, same day, same hours, is a duplicate that doubles that shift. It happens when someone taps their name twice or an entry gets imported twice. The grand total absorbs it silently. A distinct-worker count is your sanity check: if you paid 14 people this period but the count says 15, you have a phantom row to hunt down.

Once your rows are clean and in one format, verifying the sum is quick. Drop the corrected numbers into the biweekly hours calculator and compare its result against your spreadsheet total. If the two disagree, the difference tells you roughly how many hours went astray and where to look.

The habit that prevents most of this

Every mistake above traces back to correcting data after it left the system. Fix the shift at the source, keep one time format, and cap open entries automatically, and the export you hand to payroll matches reality on the first try. If you want the clock-in side to stay clean too, so workers cannot create the duplicate and orphaned rows in the first place, start a free workspace and let the entries arrive already tagged and ready to total.