Tip Printed from ExcelTip.com

---

Calculating Daily Pay, Incorporating Variable Hourly Rates


Problem:

Columns B & C contain the times signed in and out of work for each date in column A.
Column D contains the following formula, which calculates the number of worked hours per day:
=(C2-B2)*24
Range A11:B12 contains 2 types of hourly rates - one for weekdays and one for weekends.
In addition, for both of those types there is an overtime rate (cells C11:C12), which is paid for every hour over 8 daily hours.
We want to calculate the daily pay for each of the dates listed in column A.

Solution:

Use the WEEKDAY, MAX, and MIN functions as shown in the following formula:
=IF(WEEKDAY(A2,2)<=5,MAX(D2-8,0)*$C$11+MIN(D2,8)*$B$11,MAX(D2-8,0)*$C$12+MIN(D2,8)*$B$12)