Yes. The simplest Excel timesheet for multiple employees keeps one work shift per row, with columns for employee, date, start time, end time, unpaid break, and paid hours. In Microsoft Excel for Microsoft 365 on Windows, a formula can calculate each shift, while filters and summary formulas help you review hours by person or week.
Choose a layout that can grow
For a shared timesheet, use a single table rather than a separate worksheet for every employee. A row-based list is easier to sort, filter, total, and use as the source for a report. It also lets you add staff or dates without rebuilding a separate set of formulas on every tab.
Start with one row for each employee’s shift. If someone works two shifts in one day, enter two rows. A practical set of headers is:
- Employee
- Work date
- Start time
- End time
- Unpaid break
- Paid hours
- Optional: department, project, location, or approval status
For example, Jordan’s 8:00 a.m. to 4:30 p.m. shift on June 3 belongs on one row; another employee’s shift on that date belongs on another. Keep names consistent, preferably using a staff list, because variations such as “Sam Lee” and “Sam L.” can split one person’s totals in reports.
Build the timesheet in Excel
- Enter the headers in row 1 and add the shift records underneath.
- Select the range and choose Insert > Table, or press Ctrl+T on Windows. Confirm that My table has headers is selected.
- Format the date column as a date and the start and end columns as times. Format the break column as a duration, such as h:mm.
- Enter the paid-hours formula in the first data row, then fill it down or let the Excel table carry it into new rows.
- Use the filter arrows in the header row to show one employee, a date range, or a department.
On a Mac, use Command+T to create a table; the ribbon command is also available through Insert > Table. Excel for the web has table and filtering features too, though keyboard shortcuts and some ribbon placements can differ. If the sheet will be printed, set a useful print area and repeat the header row on each page through Page Layout > Print Titles in desktop Excel.
Calculate paid hours, including overnight shifts
Excel stores times as fractions of a day. Subtracting a start time from an end time therefore produces a fraction, not a decimal number of hours. A shift that crosses midnight also needs special handling; a direct subtraction can give a negative result.
With employee in A, date in B, start time in C, end time in D, unpaid break duration in E, and the first shift on row 2, enter =MAX(0,MOD(D2-C2,1)-E2)*24 in F2. If the shift is 8:00 a.m. to 4:30 p.m. with a 0:30 unpaid break, the formula returns 8. If the shift is 10:00 p.m. to 6:00 a.m. with a 0:30 break, it returns 7.5.
Format the result column as a number, such as 0.00, rather than as a clock time. The formula returns decimal hours: 7.5 means seven and a half hours. Enter breaks as durations, such as 0:30, and make sure the value is a real Excel time rather than text. The MAX function prevents a negative paid-hours result if a break is accidentally longer than the shift, but it does not flag the error; consider checking those entries separately.
This basic formula calculates elapsed time minus the break. It does not decide which hours count as overtime, apply different pay rates, or interpret a workplace policy. Those rules vary, so keep any overtime calculation separate and confirm the applicable rules with a qualified professional or the relevant authority.
Summarize hours by employee and week
A summary area can make a long timesheet easier to review. One straightforward option is a weekly total for each employee. Put the employee’s name in H2 and the first date of the week in I1. With employee names in A2:A500, work dates in B2:B500, and decimal paid hours in F2:F500, enter this formula in I2:
=SUMIFS($F$2:$F$500,$A$2:$A$500,$H2,$B$2:$B$500,
Related
- Excel Data Validation From Another Workbook: Best Methods
- Data Validation From a Table in Excel: Setup and Fixes
- Power Query Text to Columns: Split Data Step by Step
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Change the View in Outlook Sent ItemsTo change how sent messages appear, open the Sent Items folder and use Outlook’s view controls: in classic…
- Excel: Hide a Column When a Cell Contains TextExcel has no built-in formula or conditional-formatting rule that automatically hides a column when a cell contains particular…
- How to Insert a Checkbox in a Word TableTo add a clickable checkbox to a table in Microsoft Word, place the cursor in the cell where…
- How to Archive Deleted Emails in Outlook SafelyTo archive an email that is in Outlook’s Deleted Items folder, select it and move it to the…
- Excel All Caps Shortcut: The Fastest Ways to Change CaseExcel has no built-in keyboard shortcut that changes selected text to all caps. To convert text reliably, enter…
- How to Move Freeze Panes in Excel to Different RowsTo move frozen rows or columns in Excel, first choose View > Freeze Panes > Unfreeze Panes. Then…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.