Pay rates depending on time

[Dobey]

Hi all, I am trying to calculate shift rates depending on times. I have the following shift rates, but I cannot seem to get the results returned I'm looking for due to the times of some shifts going past midnight.

As an example a shift might be 15:30 - 01:30.

[TABLE="width: 725"]
<colgroup><col width="145" span="5" style="width:109pt"> </colgroup><tbody>[TR]
[TD="width: 145"][/TD]
[TD="class: xl66, width: 290, colspan: 2"]START BETWEEN[/TD]
[TD="width: 145"][/TD]
[TD="width: 145"][/TD]
[/TR]
[TR]
[TD]EARLY MORNING[/TD]
[TD="class: xl63"]04:00[/TD]
[TD="class: xl63"]05:30[/TD]
[TD="class: xl65, align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]STARTS BEFORE[/TD]
[TD]ENDS AFTER[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AFTERNOON[/TD]
[TD="class: xl64"]18:00[/TD]
[TD="class: xl64"]18:30[/TD]
[TD="class: xl65, align: right"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="class: xl66, colspan: 2"]START BETWEEN[/TD]
[TD][/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]NIGHT[/TD]
[TD="class: xl64"]18:00[/TD]
[TD="class: xl64"]3:59[/TD]
[TD="class: xl65, align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Any help would be greatly appreciated.

Regards

 

[mole999]

> https://www.mrexcel.com/forum/excel-questions/539787-subtracting-time-when-goes-into-next-day.html answer 3 onwards might suit your needs

 

[kvsrinivasamurthy]

Covering 24 Hrs give time slots from 0 Hrs to 24 hrs and respective charges in a table.

 

[thisoldman]

I can't determine how to distinguish between "afternoon" and "night". You can determine the hours worked with a formula that's not complex.

<tbody>
[TD="class: xl66"][/TD]
[TD="class: xl67, width: 64"]A[/TD]
[TD="class: xl67, width: 64"]B[/TD]
[TD="class: xl67, width: 69"]C[/TD]

[TD="class: xl66, align: right"]1[/TD]
[TD="class: xl68"]Start[/TD]
[TD="class: xl68"]End[/TD]
[TD="class: xl68"]HrsWorked[/TD]

[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl69, align: right"]04:15[/TD]
[TD="class: xl69, align: right"]12:45[/TD]
[TD="class: xl69, align: right"]08:30[/TD]

[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl69, align: right"]18:30[/TD]
[TD="class: xl69, align: right"]23:59[/TD]
[TD="class: xl69, align: right"]05:29[/TD]

[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl69, align: right"]18:30[/TD]
[TD="class: xl69, align: right"]00:01[/TD]
[TD="class: xl69, align: right"]05:31[/TD]

</tbody>

Either of these two formulas, in C2 and copied downward, will work:

=IF(A2>B2, 1, 0) + B2 - A2

or

=(A2>B2) + B2 - A2

 

[Dobey]

I can't determine how to distinguish between "afternoon" and "night". You can determine the hours worked with a formula that's not complex.

<tbody>
[TD="class: xl66"][/TD]
[TD="class: xl67"]A[/TD]
[TD="class: xl67, width: 64"]B[/TD]
[TD="class: xl67, width: 69"]C[/TD]

[TD="class: xl66, align: right"]1[/TD]
[TD="class: xl68"]Start[/TD]
[TD="class: xl68"]End[/TD]
[TD="class: xl68"]HrsWorked[/TD]

[TD="class: xl66, align: right"]2[/TD]
[TD="class: xl69, align: right"]04:15[/TD]
[TD="class: xl69, align: right"]12:45[/TD]
[TD="class: xl69, align: right"]08:30[/TD]

[TD="class: xl66, align: right"]3[/TD]
[TD="class: xl69, align: right"]18:30[/TD]
[TD="class: xl69, align: right"]23:59[/TD]
[TD="class: xl69, align: right"]05:29[/TD]

[TD="class: xl66, align: right"]4[/TD]
[TD="class: xl69, align: right"]18:30[/TD]
[TD="class: xl69, align: right"]00:01[/TD]
[TD="class: xl69, align: right"]05:31[/TD]

</tbody>

Either of these two formulas, in C2 and copied downward, will work:

=IF(A2>B2, 1, 0) + B2 - A2

or

=(A2>B2) + B2 - A2

Hours worked I have, I need to know “what shift it is” to determine shift rates. Can this be done without dates?

difference between afternoon and night
afternoon has to start before 18:00 but finish has to be after 18:30
night has to starts anytime between 18:30 and 03:59

 

[thisoldman]

Please don't hit the "Reply With Quote". It makes the thread unnecessarily long and difficult to scroll.

You keep writing "between"—what do you want to do when the clock-in time is exactly 18:00 or between 18:00 and 18:29:59?

Here's what I have:

[TABLE="class: grid, width: 259"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD="align: right"]1
[/TD]
[TD]Start[/TD]
[TD]End[/TD]
[TD]Shift[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]03:59[/TD]
[TD="align: right"]12:01[/TD]
[TD]Night[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]04:00[/TD]
[TD="align: right"]11:00[/TD]
[TD]Morning[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]04:01[/TD]
[TD="align: right"]12:45[/TD]
[TD]Morning[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]17:59[/TD]
[TD="align: right"]23:59[/TD]
[TD]Afternoon[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]17:59[/TD]
[TD="align: right"]00:01[/TD]
[TD]Afternoon[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]18:00[/TD]
[TD="align: right"]00:01[/TD]
[TD]UNKNOWN[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]18:01[/TD]
[TD="align: right"]00:01[/TD]
[TD]UNKNOWN[/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]18:29[/TD]
[TD="align: right"]00:01[/TD]
[TD]UNKNOWN[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD="align: right"]18:30[/TD]
[TD="align: right"]23:59[/TD]
[TD]UNKNOWN[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD="align: right"]18:31[/TD]
[TD="align: right"]00:01[/TD]
[TD]Night[/TD]
[/TR]
</tbody>[/TABLE]

In C2:

=IF(AND(HOUR(A2)>=4, A2<=TIME(5, 30, 0)), "Morning",
IF(AND(HOUR(A2)< 18, B2+(A2>B2)>TIME(18, 30, 0)), "Afternoon",
IF(OR(A2>TIME(18, 30, 0), HOUR(A2)< 4), "Night", "UNKNOWN")))

 

[Dobey]

Thanks thisoldman that worked a treat.

 

[thisoldman]

You're welcome. I'm happy to have helped.