How to Calculate Hours Worked in Excel: Easy Guide

Sharing is caring!

Tracking work hours accurately is essential for payroll processing, employee management, and maintaining productivity. Microsoft Excel offers a simple yet powerful way to calculate hours worked, whether for a single employee or an entire team.

In this guide, we will explain how to calculate hours worked in Excel, including regular shifts, overnight schedules, decimal hours, and breaks. Whether you’re managing a timesheet, tracking employee hours, or preparing payroll, these methods will help you set it up properly.

Set Up Your Excel Spreadsheet

Before entering any formulas, create a basic timesheet layout in Excel. You need at least three columns:

• Time In (Start Time)
• Time Out (End Time)
• Hours Worked

Here’s a sample table:

Make sure all time entries use a consistent format, such as 12-hour (e.g., 9:00 AM) or 24-hour format (e.g., 17:00).

Enter Start and End Time

In your worksheet:

• Column A: Enter the start time
• Column B: Enter the end time
• Column C: This will calculate the hours worked

For example:

Use Excel’s built-in time formatting:
Right-click cell → Format Cells → Time → Choose format like “1:30 PM” or “13:30”

Calculate Basic Hours Worked

To find the difference between Time Out and Time In, use this formula in Column C:

=B2-A2

This calculates the total time worked in hours and minutes.

Next, format the result:

1. Select the “Hours Worked” cell
2. Right-click → Format Cells
3. Choose Custom and select [h]:mm

Why [h]:mm? It lets Excel display total hours beyond 24 hours without resetting the clock.

Example:

Convert Time to Decimal Hours

Most payroll systems need hours in decimal format (e.g., 8.5 instead of 8:30).

To convert to decimals:

=(B2-A2)*24

Then, change the cell format:

• Right-click the result cell
• Format Cells → Number → Set 2 decimal places

Example:

Note: Excel stores time as a fraction of 1 day, so multiplying by 24 gives the decimal hours.

Handle Overnight Shifts (Crossing Midnight)

If an employee’s shift starts in the evening and ends after midnight, the standard formula may return a negative value.

Use this formula instead:

=IF(B2<A2, B2+1, B2)-A2

This checks if Time Out is earlier than Time In, and if so, adds 1 day to Time Out.

To get the decimal hours:

=IF(B2<A2, B2+1, B2-A2)*24

Example:

This is especially useful for night shift workers or hospital staff with non-standard hours.

Subtract Break Time

To get net hours worked, subtract break durations from the total time.

Add two more columns:

• Break Start
• Break End

Then use this formula:

=((B2-A2)-(D2-C2))*24

Assuming:

• A2 = Time In
• B2 = Time Out
• C2 = Break Start
• D2 = Break End

Here’s a table example:

Important: Use the same time format across all cells to avoid incorrect results.

Format Time and Decimal Cells Properly

To avoid issues:

• Format time columns as Time (1:30 PM) or Custom [h]:mm
• Format decimal result columns as Number with 2 decimal places

You can also apply conditional formatting or data validation to keep time inputs clean and accurate.

Total Weekly or Monthly Hours Worked

Once you’ve calculated daily hours, sum them up to get weekly or monthly totals.

Example formula:

=SUM(E2:E6)

Where E2:E6 contains the daily decimal hours.

You can adjust the range depending on how many rows you have.

Using Excel Timesheet Templates

If you’re not starting from scratch, Excel templates can save time. Look for:

• Employee Timesheet Template
• Biweekly Timesheet
• Hourly Payroll Calculator

You can customize these templates with formulas mentioned above to track:

• Work hours
• Breaks
• Overtime

Go to:
File → New → Search “Timesheet”

Apply Formulas to Multiple Rows

To apply the same hours worked formula across multiple employees or days:

1. Enter the formula in the first row
2. Use the fill handle (small square at the bottom-right of the cell)
3. Drag it down to copy the formula to other rows

Excel will automatically adjust the cell references (A2 → A3, A4, etc.)

Tips to Avoid Common Mistakes

• Always format time and number cells correctly
• Use 24-hour format if AM/PM causes confusion
• Apply proper rounding if decimals exceed two digits
• Use ROUND() function if necessary: =ROUND((B2-A2)*24, 2)
• Avoid negative time values by using the overnight shift formula
• Use data validation to prevent invalid time entries (e.g., 25:00)

Advanced Time Tracking Options

If you’re handling multiple employees or automating payroll, consider:

• PivotTables to summarize total hours per employee
• IF and IFS formulas to calculate different pay rates for overtime
• Named Ranges to make formulas easier to read
• Drop-down menus for shift types

Example:

=IF(HourWorked>8, (8*HourlyRate) + ((HourWorked-8)*OvertimeRate), HourWorked*HourlyRate)

This calculates pay for standard time and overtime.

Final Thoughts

Calculating hours worked in Excel is easy with the right setup. Use basic formulas, apply correct cell formatting, and handle edge cases like overnight shifts and breaks to ensure accurate results.

By setting up a clean structure and understanding time calculations, Excel becomes a reliable tool for tracking employee hours, simplifying payroll calculations, and improving workforce management.

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.