Keeping track of working hours is a crucial task for employees, freelancers, and businesses alike. Fortunately, Microsoft Excel provides robust tools to manage and calculate timesheets efficiently. One such tool is the ability to use formulas to calculate total hours worked, saving valuable time and minimizing human error. Let's explore how to create an Excel timesheet formula for total hours.
To get started, we'll assume you have a timesheet set up with columns for 'Date', 'Start Time', and 'End Time'. Here's how to calculate the total hours worked using these columns:
Creating the Total Hours Formula
The first step is to determine the cell where you want the total hours to be displayed. For this example, let's place our cursor in cell A6 and name this cell 'Total Hours'.
Now, we'll create a formula that subtracts the start time from the end time and calculates the duration of each workday. We can then sum up these durations to find the total hours worked.
Calculate Daily Work Hours
In cell B2, we'll enter the formula:={"End Time"2-"Start Time"2}. This will calculate the duration of the first day's work in hours. We can then drag this formula down to apply it to all working days.
Assuming your timesheet data starts from row 2 and ends at row 11, your 'Daily Work Hours' should look like this:
| Date | Start Time | End Time | Daily Work Hours |
| 01/01/2022 | 08:00 | 17:00 | =B2-A2 |
| 02/01/2022 | 08:00 | 18:00 | =B3-A3 |
| ... (repeat for all working days) |
Sum Up Daily Work Hours
Now that we have the daily work hours, we can sum them up to find the total hours worked. In the 'Total Hours' cell (A6), enter the formula: =SUM(B2:B11). This sum will automatically update as you add or remove days in your timesheet.
Your Excel timesheet should now look like this:
| Date | Start Time | End Time | Daily Work Hours |
| 01/01/2022 | 08:00 | 17:00 | 9 |
| 02/01/2022 | 08:00 | 18:00 | 10 |
| ... | |||
| Total Hours Worked | =SUM(B2:B11) | ||
With this set-up, managing your timesheet and calculating total hours worked in Excel is a breeze. Now you can focus on what really matters – putting those hours to good use!