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)
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.
