апреля 20, 2023 г.
If you're managing a team or tracking your own work hours, you may find yourself in need of a formula to calculate hours worked in Excel. While Excel offers several complex features, the process of creating a simple hours worked formula is quite straightforward. This guide will walk you through the steps to create your formula.
Understanding the Basics of Your Formula
Before you start creating your formula, you need to understand what you're calculating. The basic formula is simply:
Hours Worked = Start Time - End Time
Using Excel's Time Function
Excel doesn't directly support the subtraction of times so you'll use the TIME function to accurately calculate hours worked.
The TIME function concatenates three separate arguments (hour, minute, second) to convert them into a serial date/time. This function is essential for calculating the difference in time.
Understanding Time Serials
Time serials are the internal representation of time in Excel. They're calculated as follows:
(hours*24)+minutes/60+seconds/3600
In other words, time serials are decimals representing hours and minutes as fractions of a 24-hour day.
Setting Up Your Excel Sheet
Before you start crunching numbers, let's prepare your Excel sheet.
In the first column (Column A), input the dates for each day using the DATE function. In Column B, input the start times for each day. In Column C, input the end times.
Calculating Start Time and End Time Serials
Select a cell in Column D and input the following formula:
=((B2+IF(B2>=0.5,1,0))/24), where B2 is the start time for the selected day.
Press enter, and the cell will display the decimal value for the start time. Repeat this process for Column E, replacing B2 with C2 (end time).
Calculating Hours Worked
Finally, in Column F, input the following formula to calculate the hours worked:
=E2-D2
This formula calculates the difference in start and end times to give you the total hours worked.
Formatting Your Results
Your results should now be in Column F, but they're in decimal format. To view them in a more understandable format, right-click on the cell, select Format Cells, then Custom.
For minutes, use the format 0, which will round to the nearest minute. If seconds aren't a concern, you can use 0 again. If you need to see seconds, use the format 0.
And there you have it, your hours worked formula in Excel. This formula takes care of most scenarios, but keep in mind that it doesn't account for days when you work over 24 hours or days when you worked more than one shift. For those scenarios, you'll need to adjust your formula.
So, go ahead – optimize your workflow and never miss tracking your hours again with this powerful Excel formula. Happy calculating!