Introduction:

Calculating hours worked using the 24-hour clock, also known as the military time format, can be quite handy if you’re managing an international team or handling irregular shifts. Excel is a powerful tool that allows you to perform calculations, either simple or complex, with relative ease. This article walks you through the process of calculating hours worked in Excel using the 24-hour clock.

Step 1: Input Your Data

First, create a simple table to input your data. Use the following headers for four columns: Employee Name, Start Time, End Time, and Hours Worked. For this example, fill in the Employee Name column with names of employees and their corresponding start and end times in 24-hour format.

Step 2: Convert Times into Decimals

Excel recognizes time as fractions of a day. To perform accurate calculations, convert the start and end times into decimal format. Follow these steps:

1. Click on cell B2 (assuming this is where an employee’s start time is entered).

2. Enter the formula “= MOD(B2*24) ” without quotes and press Enter.

3. Click on cell C2 (assuming this is where an employee’s end time is entered).

4. Enter the formula “= MOD(C2*24) ” without quotes and press Enter.

These formulas will convert start and end times into decimals representing hours worked in a 24-hour period.

Step 3: Calculate Total Hours Worked

Once you have your start and end times converted into decimals, you can now calculate total hours worked.

1. Click on cell D2 (assuming this is where you want to display total hours worked for an employee).

2. Enter the formula “=C2 – B2” without quotes and press Enter.

This formula calculates the difference between end time and start time as the total hours worked in decimal format.

Step 4: Convert Decimal Hours Worked into Time Format

To convert decimal hours back into a time format, follow these steps:

1. Click on cell D2.

2. Enter the formula “=D2/24” without quotes and press Enter.

You’ve now converted total hours worked in decimals to a time format, which is more convenient for human reading.

Step 5: Copy the Formulas for Other Employees

Once you’ve entered and tested the formulas for one employee, you can apply the same formulas to subsequent rows by simply copying and pasting or dragging the fill handle.

Conclusion:

Using Excel to calculate hours worked in a 24-hour clock format is an efficient way to manage and evaluate employee’s working hours. Once you’ve set up your table and input the formulas, you can easily monitor and analyze the working patterns of your team.