Excel calculator shift night

[nerxhan]

Hello Masters,

I'm newbie on excel. I make a lot of research. I hope you can help me.

Can somebody advice me how to do this:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Turn[/TD]
[TD][TABLE="width: 124"]
<tbody>[TR]
[TD="class: xl64, width: 124"]Start First Turn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="class: xl66, width: 106"]End First Turn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="class: xl66, width: 117"]Total First Turn(minute)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl66, width: 114"]Start Second Turn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl66, width: 107"]End Second Turn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl66, width: 127"]Total Second Turn(minute)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl66, width: 119"]Start Third Turn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl66, width: 98"]End Third Turn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl66, width: 188"]Total Third Turn[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 201"]
<tbody>[TR]
[TD="class: xl65, width: 201"]Extra Time From Turn One[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 201"]
<tbody>[TR]
[TD="class: xl65"]Extra Time From Turn Two[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 201"]
<tbody>[TR]
[TD="class: xl65"]Extra Time From Turn Three[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]9:03:00 AM[/TD]
[TD][TABLE="width: 106"]
<tbody>[TR]
[TD="class: xl65, width: 106, align: right"]11:07 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 117"]
<tbody>[TR]
[TD="class: xl65, width: 117, align: right"]125[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 114"]
<tbody>[TR]
[TD="class: xl65, width: 114, align: right"]10:16 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 107"]
<tbody>[TR]
[TD="class: xl65, width: 107, align: right"]11:21 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 127"]
<tbody>[TR]
[TD="class: xl65, width: 127, align: right"]65[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 119"]
<tbody>[TR]
[TD="class: xl65, width: 119, align: right"]11:45 PM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 98"]
<tbody>[TR]
[TD="class: xl65, width: 98, align: right"]2:13 AM[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 188"]
<tbody>[TR]
[TD="class: xl65, width: 188, align: right"]148[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I calculate Total minute from the turn one, two, three, but I have a big problem to get minutes if they are on "night shift one" or "night shift two" from "turn one", "turn two", "turn three".

The first night shift
start at: 22:00
end at: 24: 00

The second night shift
start at: 24:00
end at: ~5:00



Thank you so much.

 

[steve the fish]

Hi. With your data pasted into A1 try this in C2:

=IF(B2>A2,(B2-A2)*1440,("24:00"-A2+B2)*1440)

Then copy and paste it into F2 and I2.

 

[nerxhan]

Hi Steven, thank you for your fast response. I really appreciated

My problem is to get minutes which belong to The first night shift time or if belong to The second night shift time from "start time" and "end time" in J2, K2, L2

Thanks

 

[steve the fish]

Sorry you have lost me. What is it you require looking at that data.

 

[nerxhan]

Sorry for my English.

I'm looking for solution on J2, K2, L2

I need to see if Start Turn and End Turn is on The first night shift (start at: 22:00, end at: 24: 00) or in The second night shift (start at: 24:00 end at: ~5:00).
i.e. if Start turn is 23:53 and End turn 00:14 i need to get 7 minutes from The first night shift and 14 minutes from The second night shift on J2 column

 

[steve the fish]

If im understanding its something like:

=IF(B2>A2,(B2-A2)*1440,("24:00"-A2)*1440) for before midnight
=IF(B2>A2,(B2-A2)*1440,B2*1440) for after midnight

 

[nerxhan]

It's not working .

It's related to this question http://www.mrexcel.com/forum/excel-...ng-day-shift-night-shift-hours-date-time.html
but I don't know, for me doesn't work.

Thanks again

 

[steve the fish]

Well you may have to change the ranges. Did you do that? I cant see your sheet.

 

[nerxhan]

I'm trying to upload the file, but I don't know how to. Maybe I haven't right to upload

 

[nerxhan]

Here is the file.
https://www.wetransfer.com/downloads/929aa3efb7b185d4eba2613c41fb178620151030155543/2f16d3