Beta Dev
OnTheClock - Employee Time Tracking
  • What We Offer
    Online Time Clock
    Payroll
    Time Off
    Tracking (PTO)
    Shift Scheduling
    GPS
    All Features
    Benefits
    Benefits
    that make your life easier
    How It Works
    Setup in
    2 minutes or less
    Integrations
    Seamless
    Payroll Integrations
  • Pricing
  • Resources
    Company
    • Case Studies
    • OTC Blog
    • Webinars
    • Help
    • Referral Program
    Free Tools
    • For Accountants
    • Accountant Marketplace
    • Time Card Calculator
    • Decimal Hours Converter
    • Time Duration Calcuator
    • Military Time Converter
    • PTO Calculator
    • U.S. Business Holidays
    • More Free Tools
  • About Us
    • Meet Us
    • Reviews
    • Philanthropy
    • Our Brochure
    • Careers
  • Contact Us
    Contact Us
    Request A Demo
    Call Us
    (888)-753-5999
Try It Free! Mobile Login OnTheClock Mobile Login Button for registered users Login
Search Search OnTheClock Website
Try It Free! Login OnTheClock Login Button for registered usersLogin

How to Make an Excel Time Sheet Like a Pro

July 12, 2023 - By OnTHeClock
How to Make an Excel Time Sheet Like a Pro

How to Make an Excel Time Sheet Like a Pro

A complete guide to time spreadsheets in Excel, with tutorial and templates.

Excel Time Sheet

If you need a way to track hours worked, we’re here to help. Even if you’re not naturally talented at spreadsheets, time tracking with Excel isn’t too complicated. However, it does take some time to set up, as well as to log hours each week.

Here’s how to make a time spreadsheet in Excel, step by step.

Table of Contents

1 Step 1: Create your table.
2 Step 2: Add dates and times.
3 Step 3: Add hour types.
4 Step 4: Add identifying information.
5 Step 5: Adding formulas.
6 Step 6: Make it pretty.
7 Step 7: Adding hours worked.
8 Step 8: Printing.
9 Excel Time Sheet Templates
10 Can you use Excel as a time clock?
11 Time Tracking in Excel: Pros and Cons
12 Streamline Your Business Operations with OnTheClock
6 The Future of Time Tracking in the Workplace

Step 1: Create your table.

First, the basics. Take note of the “Merge and Center” function, which we’ll use often throughout this tutorial.

  1. Launch Microsoft Excel and create a blank workbook
  2. Select columns A1-K1. Navigate to “Home,” then select “Merge and Center.”
  3. Select columns A2-K2, then “Merge and Center.”
  4. Type a time Sheet title into the merged cells. (Your company name works.)

If you’re creating an Excel time sheet for your staff, your title may be your company name. If you’re invoicing for hours worked, your time sheet title may include the title of your LLC.

Step 1

Step 2: Add dates and times.

Every time sheet needs time data. Let’s add ours. For this tutorial, we’ll make a weekly time sheet.

  1. Highlight cells A3-B3, then Merge & Center. Type “Dates” into this field
  2. In column A4, type the start day of your workweek (e.g., Monday).
  3. Hover your cursor over the lower right corner of cell A4. It should transform into a plus sign.
  4. Click and drag to highlight cells A4-A10, then release. This will auto-fill the rest of the days of your workweek.
  5. Highlight cells C4-F4.
  6. Right-click on the highlighted area, and choose “Format Cells.”
  7. Select “Time,” then choose your preferred time format.
Step 2 How to Make an Excel Time Sheet

Step 3: Add hour types.

Next, you’ll want to add days and times to your Excel sheet. Each date will have 2 start and end times. That way, you can account for a break.

  1. Type “Start Time” into cells C3 and E3.
  2. Type “End Time” into cells D3 and F3.
  3. Type “Regular Hours” into cell G3.
  4. Type “Vacation” into cell H3.
  5. Type “Sick” into cell I2.
  6. Type “Holiday” into cell J2.
  7. Type “Regular Hours” into cell F11.
  8. Type “Overtime” into cell F12.
  9. Type “Total Hours” into cell F13.
  10. Type “Hourly Rate” into cell F14.
  11. Type “Overtime Rate” into cell F15.
  12. Type “Gross Pay” into cell F16.
  13. Select cells G14-16.
  14. Right-click and select “Format Cells.”
  15. Under “Number,” select “Currency.”
  16. Enter the hourly pay rate into cell G14.
  17. Enter the overtime rate into cell G15.

After you finish these formatting steps, you need to enter pay rates.

Step 3 How to Make an Excel Time Sheet

Step 4: Add identifying information.

To make your time sheet a functional record, you need to create a section for identifying information.

  1. Type “Employee” into cell H11. Select cells I11-J11, then “Merge & Center.”
  2. Type “Employee ID” into cell H12. Select cells I12-J12, then “Merge & Center.”
  3. Type “Manager” into cell H13. Select cells I13-J13, then “Merge & Center.”
  4. Type “Department” into cell H14. Select cells I14-J14, then “Merge & Center.”
  5. Type “Pay Period” into cell H15. Select cells I15-J15, then “Merge & Center.”
  6. Type “Signature” into cell H16. Select cells I16-J16, then “Merge & Center.”

If you plan on filling your time sheet by hand, you can skip to step 6 to finish formatting. Alternatively, you can make a fully functional digital time sheet with Excel formulas.

Step 4 How to Make an Excel Time Sheet

Step 5: Adding formulas.

Now onto the fun part: Excel formulas. While adding sums in Excel is relatively easy, it gets complicated when weekly overtime comes into play. We’ve provided a spreadsheet formula that automatically calculates overtime after 40 hours per week.

In addition to calculating weekly overtime hours, we’ll need to factor vacation, sick, and holiday hours into our total hours. This will allow gross pay to be accurately calculated.

Note: This Excel spreadsheet formula calculates overtime after 40 hours are worked within a week. This is the most common overtime law in the United States. If you calculate overtime at a daily rate, you can use the sum function instead.

Calculating Daily Total Hours

In cell G4, copy and paste the following formula:

=IFERROR(IF(I4>=8, IF(ISNUMBER(H4)=TRUE, H4, IF(ISNUMBER(I4)=TRUE, I4, IF(ISNUMBER(J4)=TRUE, J4, HOUR(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))+MINUTE(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))/60+SECOND(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))/3600)))), IF(ISNUMBER(I4)=TRUE, I4+IFERROR((HOUR(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))+MINUTE(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))/60+SECOND(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))/3600),0), IF(ISNUMBER(H4)=TRUE, H4, IF(ISNUMBER(J4)=TRUE, J4, HOUR(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))+MINUTE(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))/60+SECOND(IF(AND(NOT(ISBLANK(C4)), NOT(ISBLANK(D4)), NOT(ISBLANK(E4)), NOT(ISBLANK(F4))), (D4-C4)+(F4-E4), ""))/3600)))), 0)

Then, hover your cursor in the bottom right corner of G4. It should transform into a plus sign. Click and drag to highlight cells G4-G10, then release. This will autofill the formula to the remaining cells for the workweek.

Calculating Weekly Total Hours

To calculate regular hours, copy and paste the following formula into cell G11:

=IF(SUM(G4:G10)>40,40,SUM(G4:G10))

To calculate weekly overtime, copy and paste the following formula into cell G12:

=SUM(G4:G10)-G11

To calculate total weekly hours, copy and paste the following formula into cell G13:

=(G12+G11)

Finally, to calculate weekly gross pay, enter the following formula into cell G16:

=(G11*G14)+(G12*G15)

Now, you’ve got a functional weekly time sheet in Excel. Congratulations!

Step 5 How to Make an Excel Time Sheet

Step 6: Make it pretty.

While our Excel time sheet is fully functional, it could look nicer. There are many fun ways to spruce up your spreadsheets and add a personal touch. We’ll cover some formatting basics:

  • Formatting Fonts to help you choose an easy-to-read font.
  • Sizing Columns to give a uniform appearance.
  • Adding Borders to organize data into sections.
  • Color Fills to make it easy to visually understand data.

For now, let’s do some basic formatting to make your time sheet look professional and easy to read.

Formatting Fonts

  1. Select all. You can do this with a keyboard shortcut: Ctrl + A on Windows or Cmnd + A on Mac.
  2. Right-click to bring up font format options.
  3. Click on the font name and select one that’s easy to read. (We chose Arial.)
  4. Click on the number next to the font name to change the font size. (We chose 12.)
  5. To make it easier to read, bold your column and row headers (found in row 3, column F, and column H).
  6. Finally, right-click on your spreadsheet title (i.e., your company name).
  7. Format your title to stand out. (We changed the font to Arial Black, size 22.)

Sizing Columns

  1. Select all.
  2. Right-click on “A” at the top of the screen.”
  3. From the menu, select “Column Width.”
  4. Enter a value to make columns equal width; we choose “16” based on our font size.

Adding Borders

  1. Select A3-J10. Right-click to bring up the format menu.
  2. Click on the arrow next to the Border icon.
  3. Choose “Thick Outside Border.”
  4. Repeat on cells F11-G17 and H11-J17.
  5. Now, we’ll add a vertical line. Select G3-G17.
  6. Right-click, then click on the arrow next to the Border icon.
  7. Select “Right Border.”

Adding Color Fills

  1. Select headers (A3-J3, F11-F17, and H11-16).
  2. To select multiple headers, hold down the Ctrl (Windows) or Cmnd (Mac) keys while dragging your cursor.
  3. Right-click, then click on the arrow next to the fill button (which looks like a paint bucket).
  4. Select your preferred color to fill the headers. (We chose light orange).
  5. Select A5-J5, A7-J7, A9-J9, then navigate back to fill colors.
  6. Fill these areas in with light gray.
  7. Select G11, G13, and G15, then fill with light gray.
  8. Select J11, J13, and J15, then fill with light gray.

Step 7: Adding hours worked.

We’re finally ready to fill out the time sheet. If you prefer to fill your time sheet by hand, skip to the next step. Otherwise, here’s how to add data electronically.

  1. Type in applicable identifying data for rows I11-I15.
  2. In cell B4, enter the start date for the pay period as MM/DD/YYYY (e.g., Jul 3, 2023, would be 07/03/2023).
  3. Hover your cursor over the lower left corner of cell B4. It should transform into a plus sign.
  4. Click and drag to highlight cells B4-B10, then release. This will autofill the dates for the rest of the week.
  5. Enter the start and end times for each date.
  6. Enter any applicable vacation, sick, or holiday hours for each date.

You now have a digital file ready for payroll processing. If you’re using payroll provider software, you can export your file as a CSV for easy importing.

Step 7 How to Make an Excel Time Sheet

Step 8: Printing.

Printing a hard copy of your Excel time sheet requires a little setup.

  1. Navigate to “File,” then “Print.”
  2. Set the page orientation to “Landscape.”
  3. Set the scaling to “Fit Sheet on Page.”
  4. Navigate to “Page Setup,” then “Margins.”
  5. Check the boxes to center horizontally and vertically on the page.

Now, you can print a clean copy of your spreadsheet to be signed and stored as a hard record.

Excel Time Sheet Templates

Looking for a time sheet template? We’ve got you covered.

  • Daily Time Sheet Template
  • Weekly Time Sheet Template
  • Biweekly Time Sheet Template
  • Monthly Time Sheet Template

Go ahead and download — our templates are free!

Can you use Excel as a time clock?

Thanks to the “Now” function, setting up an Excel spreadsheet like a time clock is possible. Employees could simply type =NOW() and press enter, imitating a punch into a time clock. You can even add buttons to perform this function, so that workers can click to clock in or out.

But let’s not oversimplify. It is pretty time-consuming to configure this type of time clock. And once you get it working, keeping employee data stored and organized is another complicated issue to resolve. Finally, because Excel isn’t time clock software, using it in this way makes it prone to human error, and puts you at risk of time theft.

Creating an Excel time clock could be fun if you have a passion for spreadsheets, coding, or design. However, we don’t recommend it for the average user. If you’ve outgrown manual time tracking with spreadsheets, investing in a proper time clock system is worth it.

Ditch spreadsheets and track time easily!

No more manual timesheets — OnTheClock tracks time for you!

Time Tracking in Excel: Pros and Cons

Using time sheets in Excel is a workable solution for contractors, freelancers, small business owners, and anyone who needs to invoice clients on an hourly basis. That said? There are a few drawbacks to manually tracking time in this way.

Pros:

  • Cost-effective. If you already have Excel, you don’t need to purchase additional software.
  • Instant calculations. Excel can crunch numbers for you with formulas and functions.
  • Customizable. Excel formulas and functions allow you to create bespoke time sheets.

Cons:

  • Learning curve. If you’re new to Excel, it can be challenging to learn how to use it.
  • Error-prone. Excel time sheets may be unreliable, as data can be easily manipulated or entered incorrectly.
  • Inconvenient. Excel is powerful, but lacks automation, reporting, and alerts.

For many, manually tracking hours worked in a spreadsheet is a place to start. But it may be cumbersome if you’re managing payroll for a large staff, or a portfolio of billable clients. If that’s the case, automating time tracking might be a worthy investment.

Streamline Your Business Operations with OnTheClock

A good time tracking software can be configured to meet your unique business needs. Employees clock in and out with a single tap. When it’s time to process payroll, you can send timecard data to your provider in just a few clicks.

If you’re ready to evolve past manual time tracking, OnTheClock offers 30 days for $0 to new users. Explore our features, schedule a demo, or start your free trial now.

Read more content like this

Check out the other posts we have written related to this article.

  • Time Card Fraud and How to Stop It
  • Late time sheets? Here’s what to do.
  • How to Fill Out Time Sheets Like a Pro
  • Selecting the Best Type of Time Clock for Your Business
  • Decoding Efficiency: Understanding How Time Tracking Software Works

Stop Spending Hours on Spreadsheets

With OnTheClock, get things done in minutes!

Follow us on social media

LinkedIn OnTheClock TikTok Icon Instagram OnTheClock Instagram Icon LinkedIn OnTheClock LinkedIn Icon Twitter OnTheClock Twitter Icon Facebook OnTheClock Facebook Icon YouTube OnTheClock YouTube Icon
OnTheClock Employee Time Tracking

Written by

OnTheClock Team

OnTheClock is the perfect app for business that want to keep track of their employees' time without spending hours doing it. With OnTheClock, you can forget about the old way of doing things.

LinkedIn OnTheClock TikTok Icon Instagram OnTheClock Instagram Icon LinkedIn OnTheClock LinkedIn Icon Twitter OnTheClock Twitter Icon Facebook OnTheClock Facebook Icon YouTube OnTheClock YouTube Icon

Do you want to know more about how OnTheClock works?

Schedule a Free Demo

Leave Your Thoughts...

(required, will be shown)
(required, will not be shown)
OnTheClock - Online Employee Time Clock Online Employee Time Clock, Simple & Powerful, PTO, Scheduling | OnTheClock
is available for...
Web BrowserOnTheClock - Web Browser - Windows/Mac
Web Browser
Windows/Mac
Google PlayOnTheClock - Google Play - Android App
Android
Google Play
AppleOnTheClock - Apple - Apple App
Apple iOS
App Store
Features
  • Online Time Clock
  • Mobile Time Clock
  • Payroll
  • GPS
  • Time Off Tracking (PTO)
  • Shift Scheduling
  • Payroll Integrations
Our Company
  • Call (888)-753-5999
    19176 Hall Road, Suite #260,
    Clinton Township, MI 48038
  • Careers
  • Meet Us
  • Blog
  • Philanthropy
Customers
  • Customer Reviews
  • Customer Success Stories
  • Help Topics
  • FAQ
  • Helpful Videos
  • Uptime Reports
  • Webinars
Free Resources
  • Free TimeCard Calculator
  • Decimal Hours Converter
  • Time Duration Calculator
  • Excel Timesheet Calculator
  • Free Timesheet / Time Card Template
  • United States Business Holidays
  • More Free Tools
Join us on Social Media
We'd love to hear from you
LinkedIn OnTheClock TikTok Icon Instagram OnTheClock Instagram Icon LinkedIn OnTheClock LinkedIn Icon X OnTheClock X Icon Facebook OnTheClock Facebook Icon YouTube OnTheClock YouTube Icon Contact Us OnTheClock Contact Icon
© 2025 OnTheClock.com, LLC
Made with Love OnTheClock Made In Michigan Love Icon in Michigan
  • Privacy
  • Terms
  • SaaS Agreement
Close
WOW