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.

	START BETWEEN
EARLY MORNING	04:00	05:30
	STARTS BEFORE	ENDS AFTER
AFTERNOON	18:00	18:30
	START BETWEEN
NIGHT	18:00	3:59

<colgroup><col width="145" span="5" style="width:109pt"> </colgroup><tbody>
</tbody>

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.

	A	B	C
1	Start	End	HrsWorked
2	04:15	12:45	08:30
3	18:30	23:59	05:29
4	18:30	00:01	05:31

<tbody>
</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.

	A	B	C
1	Start	End	HrsWorked
2	04:15	12:45	08:30
3	18:30	23:59	05:29
4	18:30	00:01	05:31

<tbody>
</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:

	A	B	C
1	Start	End	Shift
2	03:59	12:01	Night
3	04:00	11:00	Morning
4	04:01	12:45	Morning
5	17:59	23:59	Afternoon
6	17:59	00:01	Afternoon
7	18:00	00:01	UNKNOWN
8	18:01	00:01	UNKNOWN
9	18:29	00:01	UNKNOWN
10	18:30	23:59	UNKNOWN
11	18:31	00:01	Night

<tbody>
</tbody>

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.