Today we are going to discuss about different methods in Excel to calculate worked hours.
Before that we will get some idea about how excel handles time. The issue looks simple from the surface, but lot to learn, let's dive in.
Here, our data resides in the range B3:D8.
We have shift type, Start time and End Time.
All we need to know is hours worked.
How excel handles Date and Time?
In cell C13, we do have date i.e.,25-04-2024.
But in excel, No data type for dates.
This is just number formatted as date. If we want to take a look at the number behind the date, press Alt + H + N + G and Enter to get into the General format.
Else, we can do the same by below method.
Menu bar -> Home tab -> Number Format -> select General.
Number behind the date 25-04-2024 is 45407. If we format, number 1 into date, it will show us 01-01-1900, this is where date starts in excel.
So, minimum possible date in excel is 01-Jan-1900.
If we change the format to general in C14 cell, it shows the extraneous decimal number as shown below. 08:14 PM became 0.84305555555556.
How did we get this decimal number?
24 hours are there in a day. So, one hour in a day is equal to 1/24 i.e. 0.0416666666666667.
Early morning 4 AM is equal to 4/24,
i.e. 0.166666666666667.
12 Noon in a day is 12/24, that is 0.5. Now you get some idea about time.
If we have both date and time as in C15 cell,
Integer part is date and decimal part is time. remember that.
Let me show you the number behind that,
Now, we can get into our requirement to find the worked hours. Since, we have decimal numbers behind the time, we can simply do End time - Start time,
to get the difference.
The formula in E4 cell is,
=$D4-$C4
Since we have time format in Adjacent column (D), Excel copies the same format in E column. No issue, we will change the format based on our requirement.
If the person worked for 7 hours and 30 minutes, then we can show that as 07:30 by setting the format 'hh:mm'.
To change the format in E4 cell, Press Ctrl + 1 to get the format cells dialog box.
In the dialog box, click on the Number tab at the top left corner,
In category, select custom and in the 'type' box enter hh:mm, then click OK.
Then drag the formula till E8 cell.
For day shift it shows correct result but for night shift it throws error.
Let me decode this, to understand the error.
In day shifts, usually, end time is larger than start time. In that case, we can easily find the difference in hours. So, no issue in day shifts.
In night shifts, start time is larger and end time is smaller. 2nd row in our data,
Start time is 0.729166666666667,
End time is 0.135416666666667.
When we detect that, we obviously get negative number.
But we cannot format the negative number into time.
Let me show you 3 ways to overcome this.
Method 1:
The formula in E4 cell is,
=IF((D4-C4)>0,D4-C4,D4-C4+1)
Arguments of IF function are,
=IF(logical_test,[value_if_true],[value_if_false])
In logical test argument, we check,
Is (end time - start time) is greater than zero,
If it is day shift, the result is Yes, so TRUE. Then simply do end time - start time.
If it is night shift, the result is No, so FALSE. Then do (end time - start time) + 1.
Let me do the evaluation step by step in E5 cell formula,
Select the cell to be evaluated,
In menu bar ->Formulas tab -> Formula Auditing group ->Evaluate formula
In dialog box, click evaluate each time to see the how excel calculates.
Step 1: Step 2:
Step 3: Step 4:
Step 5:
Result of this calculation is 0.40625.
When we format this as time, we get 09:45.
So, worked hours is 09 hours 45 minutes.
Method 2:
If the end time is smaller than start time, we add 1 and we get correct result.
If we understand this statement, we can simplify the formula we used in method 1.
The formula in E4 cell is,
=D4-C4+(C4>D4)
Just take a look at the logical test within parenthesis,
we wrapped this logical test in parenthesis to give precedence in calculation.
So, this will get evaluated first.
Look into E4 cell,
Is start time is greater than end time?
No, so the result is FALSE. When we do mathematical operation in Boolean value, FALSE gets converted into zero.
So, the formula in E4 cell is,
End time - start time + 0.
Look into E5 cell,
Is start time is greater than end time?
Yes, so the result is TRUE. When we do mathematical operation in Boolean value, TRUE gets converted into 1.
So, the formula in E4 cell is,
End time - start time + 1.
So, we did the same thing as we did in method 1, but here, we shrunk the formula.
Method 3:
This is even smaller,
The formula in E4 cell is,
=MOD(D4-C4,1)
Arguments of MOD function is,
=MOD(number,divisor)
After dividing the number by divisor, MOD function delivers the reminder from the nearest previous integer.
In E4 cell, Numerator is 0.40625 and denominator is 1. The result is 0.40625, nearest smaller integer is zero, so it gives the reminder from 0 and the final result is 0.40625.
In E5 cell, Numerator is -0.59375 and denominator is 1. The result is -0.59375, nearest smaller integer is -1, so it gives the reminder from -1 and the final result is 0.40625.
Bonus Trick:
Behind the scenes, MOD does this,
MOD(n,d) = n-d*INT(n/d)
In our case, d is always 1, then the formula is = n-INT(n).
Comentários