Explanation
In this example, the goal is to calculate durations in “days” starting from the start date and time in cell G5 and ending at the dates and times shown in column B. The twist is that we want to classify the durations using the custom labels shown in column E, starting with “Day 0” for the first 24 hours and ending at “Day 3+” for durations greater than 72 hours.
The first step is to calculate the decimal hours as seen in column C. We could also work with Excel time , but decimal hours are more convenient when we calculate the day labels later below. The formula in C5, copied down, is:
=(B5-start)*24
We subtract the start time from the end time and multiply by 24. This works because Excel times are fractional values of days .
Next, we calculate the decimal days that appear in column D by subtracting the start time from the end time. The formula in D5, copied down, is:
=B5-start
This works because Excel dates are just serial numbers . These values are only for reference, and are not used in any subsequent calculations.
Finally, to calculate the day labels as seen in column E, we use a formula based on the IFS function with 4 logical conditions:
=IFS(C5<=24,"Day 0",C5<=48,"Day 1",C5<=72,"Day 2",C5>72,"Day 3+")
For each logical test , we supply a text value that works like a bucket to collect times the appropriate day range. The IFS function is new in Excel 2019. If you don’t have IFS available in your version of Excel, you can use a formula that “nests” together several IF functions :
=IF(C5<=24,"Day 0",IF(C5<=48,"Day 1",IF(C5<=72,"Day 2",IF(C5>72,"Day 3+"))))
For more information on nesting IFs, see: 19 tips for nested IF formulas .
Explanation
Note: it’s important to understand that Excel deals with time as fractions of a day . So, 12:00 PM is .5, 6:00 AM is .25, 6 PM is .75, and so on. This works fine for standard time and date calculations, but in many cases you’ll want to convert times to decimal hours to make other calculations more straightforward. In the example shown on this page, we capture time in native units, but then convert to decimal hours in column E.
To calculate total hours worked, cell E5 contains:
=(D5-C5)*24
This is simply end time minus start time, multiplied by 24 to convert to decimal hours. If you need to calculate elapsed time that crosses midnight, see this page for options and general explanation .
To calculate regular time, F5 contains:
=MIN(8,E5)
This is an example of using MIN instead of IF to simplify . The result is the smaller of two options: 8 hours, or regular time as calculated above.
To calculate OT (overtime), G5 contains:
=E5-F5
Not much to see here. We simply subtract regular time from total hours to get overtime. Note the result will be zero if total time = regular time. This is important because it effectively “zeroes out” the overtime component of the formula in I5 when there is no overtime.
To calculate the Total, I5 contains:
(F5*H5)+(G5*H5*1.5)
This is where we finally calculate a total based on rate and hours, taking into account overtime paid at 1.5 times the normal rate. (Adjust the multiplier as needed). We first multiply regular time by the normal rate. Then we multiply overtime by the same rate times 1.5. As mentioned above, when overtime is zero, this part of the formula returns zero.
Finally, the sum of both calculations above is returned as the Total in column I.
