Yes. Excel can track biweekly timesheets by recording each shift in a 14-day date range, calculating hours, and adding each employee’s hours for that period. A simple workbook needs a period start date, one row per shift, and formulas that account for overnight work and unpaid breaks.
What “biweekly” means for a timesheet
Biweekly usually means once every two weeks. A timesheet for that schedule covers 14 consecutive calendar days, such as Monday through the second Sunday. Employers often use this schedule because it creates a regular rhythm for submitting and approving hours.
It is not the same as semimonthly pay. A semimonthly schedule has two pay periods each month, often ending on fixed dates such as the 15th and the last day of the month. That produces 24 periods per year; a biweekly schedule generally produces 26, though the alignment of dates can occasionally create a 27th period in a calendar year. Check the employer’s stated pay-period dates rather than assuming that “twice a month” and “every two weeks” mean the same thing.
A timesheet records time worked; it does not, by itself, establish how payroll must treat overtime, breaks, holidays, or other categories. Those rules depend on the employer’s policies and applicable requirements. Treat the spreadsheet as a recordkeeping and calculation aid, and confirm pay-period definitions and payroll rules with the responsible payroll contact.
Plan the workbook before entering hours
Keep the period dates separate from the shift log. For example, put the first day of the pay period in B1 and the last day in B2. Make a shift log with these headings in row 5:
- A: Work date
- B: Employee name or ID
- C: Start time
- D: End time
- E: Unpaid break duration
- F: Hours worked
Enter one row per shift, starting on row 6. If an employee works two separate shifts in one day, use two rows. This makes the entries easier to inspect and avoids trying to squeeze different start and end times into one cell. For a single-person sheet, the employee column can still be useful if the workbook may later be reused for a team.
Use actual Excel dates and times, not text that only looks like a date or time. Format dates consistently and enter times such as 8:30 AM or 5:00 PM. Enter a 30-minute break as a duration of 0:30, not the number 30. A clear layout helps catch mistakes before the hours are totaled.
Set the 14-day date range
Enter the actual first day of the pay period in B1. With the period start date in B1, enter this in B2 to calculate the final day of a 14-day period:
=B1+13
For example, if B1 contains November 2, 2026, B2 returns November 15, 2026. The addition is 13 because the start date counts as day one. To calculate the first day of the next consecutive period from B1, use:
=B1+14
That returns November 16, 2026, for the same example. Set the result cells to a date format so Excel displays dates rather than serial numbers. Confirm that the dates match the official pay calendar before relying on the sheet; the first day of a pay period is not necessarily the first day of a month or a workweek.
Calculate shift hours, including overnight work
With start time in C6, end time in D6, and an unpaid break duration in E6, enter this formula in F6 and fill it down the shift log:
=IF(OR(C6="",D6=""),"",24*(MOD(D6-C6,1)-E6))
The formula stays blank until both time cells are filled. MOD handles an end time after midnight by treating it as the following day. For a shift from 10:00 PM to 6:30 AM with a 0:30 break, the result is 8 hours: the elapsed time is 8.5 hours, less the half-hour break. The multiplication by 24 converts Excel’s time fraction into decimal hours.
Keep F in a number format with an appropriate number of decimal places, such as two, so 7.5 represents seven and a half hours. If you prefer a duration display such as 7:30, use a duration-based formula and format the result as [h]:mm instead; ordinary clock-time formatting can wrap after 24 hours. Avoid mixing decimal-hour values and time-duration values in the same total.
This formula assumes that the date in A6 is the shift’s start date and that a shift lasts less than 24 hours. A shift with identical start and end times is ambiguous to the formula, and a shift lasting exactly 24 hours would need a different method. For unusual shifts, record full date-and-time values or enter the shift as separate dated segments. Also decide how to allocate an overnight shift that crosses midnight: the example counts its full duration on the date it started, which may not suit daily reporting or payroll rules.
Total hours for each employee and pay period
Create a small summary with employee names or IDs in G6 downward and total hours beside them in H6 downward. With shift dates in A6:A100, employee names in B6:B100, calculated hours in F6:F100, the employee to summarize in G6, and the period boundaries in B1 and B2, put this formula in H6:
=SUMIFS($F$6:$F$100,$B$6:$B$100,$G6,$A$6:$A$100,">="&$B$1,$A$6:$A$100,"<="&$B$2)
It adds the hours for the employee named in G6 only when the work date falls on or after B1 and on or before B2. Copy it down for other employees. For example, if the matching shift rows contain 8, 7.5, and 8 hours, the total is 23.5 hours. The formula assumes the date in column A is the date used to assign the entire shift to a pay period.
Extend the ranges if the log may exceed row 100, or use an Excel Table so the log can grow more conveniently. To make a table, select the log and choose Insert > Table, then confirm that the table has headers. Tables expand as rows are added and can make a busy log easier to filter. If you use a table, check that new formula rows are being filled consistently.
Checks that prevent common timesheet errors
A correct-looking total can still come from incorrect entries. Add a brief review routine before submitting or approving a sheet:
- Check that every shift has a date, employee, start time, and end time.
- Confirm that break entries are durations in the format you chose, not minutes entered as whole numbers.
- Compare unusually long or short shifts with the original schedule or time record.
- Look for duplicate rows, missing days, and dates outside the stated 14-day range.
- Recheck overnight shifts and confirm that the date assignment matches the reporting method being used.
- Compare the final total with the employer’s approved timekeeping process before using it for payroll.
Blank rows deserve particular attention. The guarded hours formula above avoids showing a misleading number when a start or end time is missing. A negative result can indicate that the break is longer than the shift, or that an entry was made in an unexpected format. A result that seems several hours too large may come from treating 30 minutes as 30 hours or from using an end time that belongs to another date.
For easier review, use consistent employee IDs, protect formula cells from accidental edits, and keep a separate copy of the original submitted record when that is part of the organization’s process. Worksheet protection can reduce accidental changes, but it is not a substitute for secure access controls or a payroll recordkeeping policy.
Make the sheet readable and easier to maintain
Freeze the header row so column labels remain visible while scrolling: choose View > Freeze Panes > Freeze Top Row. Apply a date format to the work-date column, a time format to start and end, and a duration format to breaks. Use a number format for decimal hours. Clear formats make it less likely that someone will mistake a time of day for an amount of work.
Filters can help an approver view one employee or a specific date without deleting other entries. Excel Tables provide filter controls in their header row. You can also use conditional formatting to highlight unusual values, but set thresholds that fit the organization’s shifts rather than treating one generic limit as universally correct. On Microsoft 365 for Windows, the ribbon path for a basic rule is Home > Conditional Formatting > Highlight Cells Rules. Excel for Mac has comparable conditional-formatting tools, though ribbon layout can vary by release.
Keep a short instruction note in the workbook: what counts as the pay-period start, how breaks should be entered, whether hours are decimal or duration values, and who reviews the sheet. That context matters when a template is copied for a new period or used by someone who did not create it.
When Excel is a good fit—and when it is not
Excel can be a practical option for an individual, a small team, or a provisional tracking process when the schedule is straightforward and someone is responsible for reviewing entries. It is flexible: a workbook can show hours by employee, date, project, or department, and the layout can be adapted without changing the underlying 14-day period.
A spreadsheet becomes harder to manage when many people edit it at once, approvals must be auditable, schedules change frequently, or payroll needs special handling for multiple work locations, paid leave, shift differentials, or complex overtime calculations. In those cases, a dedicated timekeeping or payroll system may reduce manual work and provide controlled approvals and records. Do not assume that a spreadsheet total automatically applies the right pay rules; that is a separate question from whether the arithmetic is correct.
Choose the tool based on the process, not just the number of rows. Consider who enters time, who can edit formulas, how corrections are documented, how records are retained, and whether the system can export data in the format payroll needs. A modest workbook may be sufficient when those responsibilities are clear; a shared spreadsheet without ownership or review can create avoidable errors.
Frequently Asked Questions
How many days should an Excel biweekly timesheet cover?
A biweekly sheet covers 14 consecutive calendar days, including both the first and last date. Confirm the exact start and end dates with the employer’s pay calendar.
Is biweekly the same as twice a month?
No. Biweekly means every two weeks and usually creates 26 pay periods in a year. Semimonthly means twice monthly and usually creates 24 periods, often with different period lengths.
How do I calculate an overnight shift in Excel?
Use a formula that accounts for the end time being earlier than the start time, such as the MOD-based formula above. Record the shift date consistently, since the formula alone does not split hours between calendar days.
Should I enter 30 minutes as 0.5 or 0:30?
It depends on the formula. The example in this guide expects a time duration such as 0:30 in the break column. If you choose to enter decimal hours instead, use a formula designed for that input and label the column clearly.
Can Excel calculate overtime automatically?
Excel can apply a formula to a defined threshold, but the correct threshold and treatment depend on the applicable rules and work arrangement. Confirm those requirements with payroll or a qualified professional before using an overtime calculation to determine pay.
Related
- AI and Power Query: What It Can and Cannot Do in Excel
- Power Query Split Cell Into Rows: Steps and Examples
- Power Query Editor: How to Split Text into Multiple Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Trim Decimal Places: Format or Change ValuesTo trim decimal places in Excel, select the cells and choose Home > Number > Decrease Decimal to…
- IFS Excel Formula Examples: Syntax, Rules, and TipsExcel’s IFS function checks conditions in order and returns the value linked to the first condition that is…
- Excel Text Compare: Formulas and Practical MethodsTo compare text in two Excel cells, use =A2=B2 for a case-insensitive comparison or =EXACT(A2,B2) when capitalization must…
- Median Formula for Class 10: Examples and StepsThe median is the middle value after data has been arranged in order. For ungrouped data, use the…
- Split Cells in Excel: Text to Columns and FormulasTo split the contents of a cell in Excel, select the cells, then choose Data > Text to…
- Excel Nested Formula Examples and How They WorkExcel nested formulas place one function inside another, so a calculation or test can supply the input for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.