Calculating Times for Pay Rates

[aussiegirlone]

Although I have asked this once before but lost comprehension of what I had
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

 

[Bernard Liengme]

You use cell refs like AA4 and Y4 without telling us what they hold.
Let's make it simple
A3: start time - a value like 14:00
B31: end time - a value like 2:00
Will end time ALWAYS be Saturday? Or could it be value like 21:30 (late
Friday)?

Friday SaturdayStartTime
EndTime Regular Night Weekend
14:00 2:00 6:00 6:00 2:00
14:30 2:00 6:00 5:30 2:00
15:00 2:00 6:00 5:00 2:00
12:00 14:00 2:00 0:00 0:00

The last one worked only 2 hours on Friday - went home sick!

Regular (D3) =MIN(0.25,B3+(B3<A3)-A3)
Night (E3) =MIN(0.25,B3+(B3<A3)-(A3+0.25))*(B3<A3)
Weekend (F3) =IF(B3<A3,B3,0)
You will need to convert times to real hours and multiply each by the pay
rate to compute money values, So if H1 hold value $10 for regula pay rate,
then =MIN(0.25,B3+(B3<A3)-A3)*24*H1 will give pay for regular hours

The Saturday start/end example: are these values stored in a different place
from the Friday/Sat times?
We do need more info to be of much help

 

[aussiegirlone]

I see that I am not very good at explaining myself so please bear with me to
explain myself better.
Firstly, I'm sorry as I had completely forgot about explaining the cell
references before sending my question in and secondly, to explain that I
need formulas that divide the total time of 12 hours into the three
categories, "the normal hours" & "the Nightshift hours" & "the Weekend
Hours". Once I have these calculations in their own cells, I can then
calculate the rate of pay.
to explain: In cell AL4, I have the formula that calculates the total time
of cells Y4 and AA4.
=IF(SUM(Y4,AA4),IF((OR(AA4="",Y4="")),"",IF((AA4<Y4),((AA4-Y4)*24)+24,(AA4-Y4)*24)),"")
In cell AR4,I also have the formula that will calculate only the normal
hours worked from the total hours of cells Y4,AA4.
=IF(SUM(Y4,AA4),MAX(0,(MIN(TIMEVALUE("18:00"),IF(AA4<Y4,AA4+1,AA4))-MAX(TIMEVALUE("6:00"),Y4))*24),"")
In cell AX4 I do not have the formula that will only calculate the
nightshift hours from the total hours in cells Y4,AA4; as mentioned this
formula =(IF(AA4<Y4,AA4+1,AA4)-Y4)*24-AR4 is giving me the wrong answer, as
nightshift hours start at 18:01 and ends at 23:59 the answer should be six
not eight.
and in cell BA4 I do not have the formula that will calculate only the
weekend hours (Late Friday Night) from the total hours in cells Y4,AA4

so to answer your questions:
Question 1) The cell ref is where the two time(s) had been entered: e.g. Y4
= 14:00 and AA4 is 02:00. Formatted as h:mm
Question2) a difficult question to answer as End time AA4 is 02:00 is late
friday night which does make it Saturday
Question 3) In cell AG4, the formula calculates and converts the two
time(s) mentioned and shows the total hours into real hours which has been
formatted as general. The converted time in AG4 = 12 Hours
Question 4) each value is stored into a different place E.g. 14:00 is in
cell Y4 - 02:00 is in cell aa4 but the cells above these are merged Y3:AA3
and is labelled Friday. and this is done for each day of the week e.g E4:G4
refers to Sunday I4:K4 refers to Monday M4:O4 refers to Tuesday and AC4:AE4
refers to Saturday. although each day has its own time values they can lead
into the next day by a value like 21:30, so fridays starttime is 14:00 and
fridays end time is 02:00
it stays in the fridays columns. I just need the time divided into the three
categories as mentioned so I can do a pay rate calculation on another sheet
which has not yet been developed.
I hope I have explain myself better this time
sincerely
aussiegirlone

 

[Bernard Liengme]

Have you tried adapting my formula to compute the three times?
best wishes

 

[aussiegirlone]

Hello Bernard
I could'nt reply earlier as I wasn't home today but I had tested your
formula's out and found that the first formula you gave Regular (D3)
=MIN(0.25,B3+(B3<A3)-A3)
gives the wrong answer, the other three you supplied has solved my problem
and I appreciate your help
Thankyou very much
aussiegirlone