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
a



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<Y 4),((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(A A4<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

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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"aussiegirlone" wrote in message
...
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 a



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<Y 4),((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(A A4<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

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<Y 4),((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(A A4<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" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"aussiegirlone" wrote in message
...
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 a



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<Y 4),((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(A A4<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

Have you tried adapting my formula to compute the three times?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"aussiegirlone" wrote in message
...
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<Y 4),((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(A A4<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" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"aussiegirlone" wrote in message
...
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 a



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<Y 4),((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(A A4<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

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
"Bernard Liengme" wrote in message
...
Have you tried adapting my formula to compute the three times?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"aussiegirlone" wrote in message
...
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<Y 4),((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(A A4<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" wrote in message
...
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
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"aussiegirlone" wrote in message
...
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 a



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<Y 4),((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(A A4<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