Explanation
Note: it’s important to understand thatExcel deals with time as fractions of a day.
If you oughta calculate elapsed time that crosses midnight,see this page for options and general explanation.
To calculate regular time, F5 contains:
This isan 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:
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.
(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.
The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.
