A

#### aussiegirlone

been doing; could someone help me again with the formulas below please?

Below is a time that has three different pay rates. The day starts on a

Friday and Ends on the Saturday. Bearing in mind the actual three pay rates

are:

Normal rate 06:00 to 18:00,

Nightshift rate 18:01 to 23:59,

Weekend rate Friday midnight 00:59 to Saturday midnight 00:01

With the actual rates above mentioned look at the time entered below:

Friday Saturday

StartTime EndTime Total Hours

14:00 - 02:00 = 12

Splitting the time above according to the pay rates

The first rate begins from 14:00 to 18:00 hours.."This part is the normal

pay rate"

The second rate begins from 18:01 to 23:59...."This part is the night shift

pay rate"

The third rate begins from 00:00 to 02:00.."This part is the weekend rate,

Sat"

This Formula calculates the Total Hours of the time above. Which =12 and

correct

=IF(SUM(Y4,AA4),IF((OR(AA4="",Y4="")),"",IF((AA4<Y4),((AA4-Y4)*24)+24,(AA4-Y4)*24)),"")

This formula calculates only the hours that are to be paid as a normal rate.

Which = 4 and correct

=IF(SUM(Y4,AA4),MAX(0,(MIN(TIMEVALUE("18:00"),IF(AA4<Y4,AA4+1,AA4))-MAX(TIMEVALUE("6:00"),Y4))*24),"")

Based on the answer of the formula above which is the normal rate, the

formula below is supposed to give the night shift rate for the Friday but

the answer is wrong as I get 8 while it should be 6

=(IF(AA4<Y4,AA4+1,AA4)-Y4)*24-AR4

I don't have the formula for a Weekend rate that would calculate the

remaining last two hours of the Friday and place it in the cell of the

Saturday which already totals the hours given for the Saturday

Saturday

StartTime EndTime TotalHours

09:00 - 18:00 = 9

=IF(SUM(AC4,AE4),IF((OR(AE4="",AC4="")),"",IF((AE4<AC4),((AE4-AC4)*24)+24,(AE4-AC4)*24)),"")

The formula above for Saturday must also pick up the third rate of hours

from the Fridays time that began from midnight 00:00 to Saturday midnight so

the total hours of 9 should = 11

End results should look like this

Start time End time

14:00 02:00 = 12

14:00 18:00 = 4 normal rate

18:01 23:59 = 6 Nightshift rate

00:00 02:00 = 2 which falls over to Saturday's Weekend Rate

Total hours worked = 12

any help is very much appreciated

aussiegirlone