Set up your timesheet structure

Start with a blank Excel spreadsheet and create column headers that match what you need to track. In the first row, type labels like "Employee Name", "Date", "Start Time", "End Time", "Hours Worked", and "Notes" — or whatever columns fit your situation. Make the header row stand out by selecting it, then clicking the bold button in the toolbar.

Below the headers, enter the dates you want to track. If you are tracking a full week, list Monday through Friday (or whatever days explore). You can type each date manually, or type the first date, then drag the small square at the bottom-right corner of that cell downward to auto-fill the rest. Excel recognizes date patterns and fills them in for you.

Leave enough rows between your headers and the totals section so you have room to add entries. A typical weekly timesheet needs about 10 to 15 rows of space, depending on whether employees work multiple shifts per day.

Key Takeaways

  • Create column headers for the information you need — employee name, date, start time, end time, and hours worked are the most common.
  • Use Excel formulas to calculate hours automatically instead of doing the math by hand, which saves time and prevents mistakes.
  • Format time entries as actual time values (like 9:00 AM) rather than text, so Excel can do math with them.
  • Add a totals row at the bottom that sums up all hours for the week, using a SUM formula that updates whenever you change an entry.

Enter time values in the correct format

Excel needs to understand that you are entering time, not just text. When you type a start time, use the format 9:00 AM or 14:30 (for 2:30 PM). Excel recognizes these patterns and treats them as actual time values. If you type something like "9am" or "9", Excel may not understand it as a time, and your formulas will not work.

After you type a time and press Enter, look at the cell. If Excel understood it correctly, the time will be right-aligned in the cell (most text is left-aligned). If it is left-aligned, Excel treated it as text. Delete it and retype using the 9:00 AM format.

Do the same for end times. If someone worked from 9:00 AM to 5:30 PM, type 9:00 AM in the "Start Time" column and 5:30 PM in the "End Time" column on the same row.

Calculate hours worked with a formula

This is where Excel saves you time. Instead of subtracting times by hand, you create a formula that does it automatically. Click on the cell in the "Hours Worked" column on the first data row (not the header). Type this formula: =(E2-D2)*24 (assuming your "End Time" is in column E and "Start Time" is in column D — adjust the letters if your columns are different).

The formula subtracts the start time from the end time, then multiplies by 24 because Excel stores time as a decimal fraction of a day. Multiplying by 24 converts it to hours. Press Enter, and the cell will show the number of hours worked.

Now copy this formula down to every row where you have time entries. Click the cell with the formula, then drag the small square at the bottom-right corner downward to the last row with data. Excel copies the formula and adjusts the row numbers automatically — so the second row uses D3 and E3, the third row uses D4 and E4, and so on.

Add a weekly total at the bottom

Below your last entry row, create a "Total Hours" label and a cell that adds up all the hours. Click the cell next to "Total Hours" and type: =SUM(F2:F6) (assuming your "Hours Worked" column is F and your data runs from row 2 to row 6 — adjust the column letter and row numbers to match your actual timesheet).

The SUM formula adds every number in that range. When you press Enter, it shows the total hours for the week. If you later change any of the individual hour entries, the total updates automatically — you do not have to recalculate it by hand.

You can add other totals the same way. If you want to track overtime (hours over 40 per week), create a formula like =IF(F7>40, F7-40, 0) in a new column. This formula checks if the total is more than 40; if it is, it shows the extra hours, and if not, it shows zero.

Format your timesheet for readability

Make your timesheet easier to read by adding borders and shading. Select all the cells that contain data (from your headers down to your totals row). Right-click and choose "Format Cells", then click the "Border" tab. Choose a border style and click "Outline and Inside" to put lines around every cell.

While you are in the Format Cells window, click the "Fill" tab to add background color to your header row. A light gray or light blue makes headers stand out without being hard to read. Click OK to explore the changes.

You can also widen columns so text does not get cut off. Double-click the line between two column letters at the top of the spreadsheet, and Excel automatically adjusts the width to fit the content. Do this for each column that feels too narrow.

Save your timesheet as a reusable template

Once your timesheet is set up the way you want, save it as a template so you can use it again without rebuilding it. Click "File", then "Save As". In the "Save as type" dropdown, choose "Excel Template" (the option will say .xltx). Give it a name like "Weekly Timesheet Template" and click Save.

The next time you need a timesheet, open Excel, click "File", then "New". Search for your template by name, and it will open a fresh copy with all your formatting and formulas already in place. You just fill in the dates and times for the new week.

If you work with multiple employees, you can create a separate sheet for each person within the same file. Right-click the sheet tab at the bottom and choose "Insert Sheet". Name it with the employee's name, then copy your timesheet structure to the new sheet. All the sheets stay in one file, making it straightforward to track everyone at once.

Frequently Asked Questions

What if someone works past midnight?

If an employee works from 11:00 PM to 7:00 AM the next day, enter the end time as 7:00 AM but add 1 to the formula. Instead of =(E2-D2)*24, use =(E2-D2+1)*24. The +1 accounts for the day change and gives you the correct number of hours.

Can I add lunch breaks that don't count as work time?

Yes. Add a "Lunch Break (minutes)" column, then modify your hours formula. Instead of =(E2-D2)*24, use =((E2-D2)*24)-(G2/60) (assuming lunch break minutes are in column G). This subtracts the lunch break minutes from the total hours.

How do I track multiple shifts in one day?

Create separate rows for each shift on the same date. For example, if someone works 9:00 AM to 1:00 PM and then 2:00 PM to 6:00 PM, use two rows with the same date. Your SUM formula will add both shifts together when you total the hours.

What if I need to track pay rates and calculate wages?

Add a "Hourly Rate" column and a "Pay" column. In the Pay column, use the formula =F2*H2 (assuming hours are in F and hourly rate is in H). This multiplies hours by the rate. Create a SUM formula below to total the pay for the week.

Can I password-protect my timesheet so employees can't change it?

Yes. Click "File", then "Info", then "Protect Workbook", and choose "Encrypt with Password". Set a password that only you know. Employees can still fill in their times, but they cannot change formulas or formatting. Write the password down somewhere safe — Excel cannot recover it if you forget it.