I have a spreadsheet tracking the time a salaried person starts, when they
leave for lunch, when they return from lunch, and when they leave at the end
of the workday. The spreadsheet totals the hours and deducts the time taken
for lunch with no problem. I want to create a formula that will use the
total net hours worked for the week and then calculate the net hourly wage if
the person was hourly. I want to ensure that wages paid are accurately
compensating the hours worked for a salaried person. I currently have a
separate formula that performs that calculation, but it requires me to deduct
40 hours, convert the remaining time into a decimal format, and enter that
into the formula. The formula I am using for that calculation is
=1000/(40+((14.2)*1.5)). The 1000 is using $1000 as an example for the
person's weekly salary, and the 14.2 are the hours worked over 40 hours,
which would be multiplied times 1.5 if the person was hourly. I use a formula
=SUM(G12:G16) that sums up the normal five-day workweek. It is formatted
using [h]:mm. I'm having trouble creating a formula that uses the resulting
total hours from this formula in the previous formula. Sorry for all the
detail, but this one has me stumped.