Master Excel: Craft Your Own Hours Worked Formula

Gregory Jul 09, 2026

Tracking hours worked is a crucial aspect of project management, employee productivity, and payroll processing. Excel, with its robust features, is an excellent tool for creating a formula to calculate hours worked. This article will guide you through the process, ensuring you understand the steps and can apply them to your specific needs.

How to Calculate Hours Worked in Excel
How to Calculate Hours Worked in Excel

Before we dive into creating the formula, let's assume you have a simple timesheet with columns for 'Start Time', 'End Time', and 'Date'. We'll use these to create our formula. If your timesheet is structured differently, you may need to adjust the formula accordingly.

Calculate Production Per Hour in Excel
Calculate Production Per Hour in Excel

Understanding the Formula Basics

Our formula will use Excel's built-in functions to calculate the duration between 'Start Time' and 'End Time'. We'll then sum these durations to get the total hours worked. Let's break down the formula step by step.

Employee Time Tracking in Excel (+ video tutorial!)
Employee Time Tracking in Excel (+ video tutorial!)

We'll use the following functions in our formula:

  • TIME: Converts a start time and end time into a decimal value representing the duration between them.
  • SUM: Adds up all the durations to give us the total hours worked.
How to Calculate Hours Worked Minus Lunch Using Excel Formulas (4 Methods) - ExcelDemy
How to Calculate Hours Worked Minus Lunch Using Excel Formulas (4 Methods) - ExcelDemy

Creating the Duration Formula

First, we need to create a formula that calculates the duration between 'Start Time' and 'End Time'. Assume your 'Start Time' is in cell A2 and 'End Time' is in cell B2. In cell C2, enter the following formula:

=TIME(B2,A2)

Calculate work hours
Calculate work hours

This formula will return the duration between the start and end times in hours and minutes. For example, if your start time is '08:00' and end time is '17:00', the formula will return '09:00'.

Calculating Total Hours Worked

Now, we need to sum up all the durations to get the total hours worked. Assume your durations are in column C, starting from C2. In a new cell, say C10, enter the following formula:

How To Count Or Calculate Hours Worked In Excel
How To Count Or Calculate Hours Worked In Excel

=SUM(C2:C9)

This formula will sum up all the durations in column C, giving you the total hours worked. If you want to add more rows, simply adjust the range in the SUM function (e.g., change C9 to C15 if you have 15 rows of data).

how to calculate hours worked
how to calculate hours worked
Time Sheet Calculator in Excel
Time Sheet Calculator in Excel
How to Calculate Hours Between Two Times in Excel (6 Methods) - ExcelDemy
How to Calculate Hours Between Two Times in Excel (6 Methods) - ExcelDemy
How to calculate work hours in Excel
How to calculate work hours in Excel
Spreadsheet To Calculate Hours Worked
Spreadsheet To Calculate Hours Worked
How To Calculate Total Work Hours Minus Lunch Time In Excel
How To Calculate Total Work Hours Minus Lunch Time In Excel
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
How to Calculate Hours Worked and Overtime Using Excel Formula - ExcelDemy
Top 21 Excel Formulas
Top 21 Excel Formulas
How to Calculate Over Time in Excel 🔥#excel #shortsvideo #exceltips #tipsntricks #exceltutorial
How to Calculate Over Time in Excel 🔥#excel #shortsvideo #exceltips #tipsntricks #exceltutorial
EXCEL TIPS
EXCEL TIPS
Calculate Working Days between two Dates in Excel: Find Workdays Excluding Holidays & Weekends
Calculate Working Days between two Dates in Excel: Find Workdays Excluding Holidays & Weekends
ms excel formula
ms excel formula
how to calculate working hours and pay
how to calculate working hours and pay
How to Convert Hours into Minutes | convert hours into minutes in Excel | Excel Tutorials |Excel2022
How to Convert Hours into Minutes | convert hours into minutes in Excel | Excel Tutorials |Excel2022
How to use the MOD function to calculate time values. 🤓
How to use the MOD function to calculate time values. 🤓
robot88 : Situs Game Online  Deposit Pulsa dengan potongan Terbaik
robot88 : Situs Game Online Deposit Pulsa dengan potongan Terbaik
How to Calculate Time in Excel / Add Time, Subtract Time Excel - Tutorial ⏰
How to Calculate Time in Excel / Add Time, Subtract Time Excel - Tutorial ⏰
Part 1: Create a Basic Excel Timesheet
Part 1: Create a Basic Excel Timesheet
how to create a sum formula in excel and wordpress - infographical poster
how to create a sum formula in excel and wordpress - infographical poster
3 Crazy Microsoft Excel Formulas That Are Extremely Useful
3 Crazy Microsoft Excel Formulas That Are Extremely Useful

Formatting the Results

Excel displays the result of the TIME function in hours and minutes (e.g., '9:00' for 9 hours). However, you might want to display the result in hours only (e.g., '9').

To do this, you can use the TEXT function. In cell C2, replace the previous formula with the following:

=TEXT(TIME(B2,A2),"[h]:mm")

This formula will display the duration in hours and minutes, but only show hours if there are no minutes (e.g., '9' instead of '9:00').

Formatting the Total Hours Worked

Similarly, you can format the total hours worked to display only hours. In cell C10, replace the previous formula with the following:

=TEXT(SUM(C2:C9),"[h]")

This formula will display the total hours worked in hours only (e.g., '9' instead of '9:00').

And there you have it! You've successfully created a formula to calculate hours worked in Excel. This formula can be adjusted to fit different timesheet structures and can be used to track hours worked for multiple employees or projects.

Remember, the key to creating effective formulas in Excel is understanding the functions and how they work together. With practice, you'll become proficient in creating complex formulas to automate tasks and streamline your workflow.

Now that you've mastered this formula, why not explore other Excel features to further enhance your productivity? There's always more to learn and discover in this powerful tool.