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.
Explanation
First, it’s important to understand that the values in the Month column (B) are actual dates, formatted with the custom number format “mmm”.
For example, B4 contains January 1, 2014, but displays only “Jan” per the custom number format.
The formula itself is based on the NETWORKDAYS function, which returns the number of working days between a start date and end date, taking into account holidays (if provided).
For each month, the start date comes from column B and the end date is calculated with the EOMONTH function like so:
EOMONTH(B4,0)
EOMONTH takes a date and returns the last day of a month. The month itself is controlled by the 2nd argument. Since in this case we want to stay in the same month, we use zero.
Finally, a list of holidays is provided as the 3rd argument to NETWORKDAYS using the named range holidays (E3:E13).
With this information, NETWORKDAYS calculates the number of working days in each month, automatically excluding weekends and holidays.
If you need more control over which days are treated as weekends, use the NETWORKDAYS.INTL function.
