Day/Night shift calculation

[Virtus]

Hello all,

In the company I work we use two time frames for different pay rate. The day shift is between 6am and 10pm and the night shift is between 10pm and 6am on the next day. How can write a code to calculate amount of hours worked in each shift? The only input fields should be start and end time.

I would like it to be like this:
[TABLE="width: 393"]
<tbody>[TR]
[TD]Example[/TD]
[TD]Start time[/TD]
[TD]End time[/TD]
[TD]Day shift[/TD]
[TD]Night shift[/TD]
[TD]Total hours:[/TD]
[/TR]
[TR]
[TD="align: right"]01.10.2012[/TD]
[TD]18:00[/TD]
[TD]6:00[/TD]
[TD]4:00[/TD]
[TD]8:00[/TD]
[TD]12:00[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I am calculating the total amount of hours worked like this:
=IF(OR(C6="";D6="");"";MOD(D6-C6;1))


Thanks!

 

[T. Valko]

Hello all,

In the company I work we use two time frames for different pay rate. The day shift is between 6am and 10pm and the night shift is between 10pm and 6am on the next day. How can write a code to calculate amount of hours worked in each shift? The only input fields should be start and end time.

I would like it to be like this:
[TABLE="width: 393"]
<TBODY>[TR]
[TD]Example
[/TD]
[TD]Start time
[/TD]
[TD]End time
[/TD]
[TD]Day shift
[/TD]
[TD]Night shift
[/TD]
[TD]Total hours:
[/TD]
[/TR]
[TR]
[TD="align: right"]01.10.2012
[/TD]
[TD]18:00
[/TD]
[TD]6:00
[/TD]
[TD]4:00
[/TD]
[TD]8:00
[/TD]
[TD]12:00
[/TD]
[/TR]
[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

I am calculating the total amount of hours worked like this:
=IF(OR(C6="";D6="");"";MOD(D6-C6;1))


Thanks!

Try this...

Book1
	A	B	C	D	E	F
1	Date	In	Out	Total	Day	Night
2	10/1/2012	6:00 PM	6:00 AM	12:00	4:00	8:00
Sheet1

This formula entered in D2:

=MOD(C2-B2,1)

This formula entered in E2:

=(B2>C2)*MEDIAN(0,C2-1/4,16/24)+MAX(0,MIN(22/24,C2+(B2>C2))-MAX(1/4,B2))

This formula entered in F2:

=D2-E2

Format D2:F2 as h:mm

 

[Virtus]

It works just as I wanted, thank you

 

[T. Valko]

It works just as I wanted, thank you

You're welcome. Thanks for the feedback!

 

[tonym99]

Todd,

Great formula. I need something like this very similar for my pilot logbook spreadsheet. The only difference is that my total time is already calculated for me and I'm able to import into the spreadsheet along with the start and end times for my flights. Here's the catch, my total time takes into account any time change that took place from takeoff and landing. So, therefore, the total time might only show 1 hr flight time when you look at the takeoff and landing time and it's 2 hours difference due a time change from say Central time zone to Eastern time zone. Therefore I don't use the formula for total time like you have in the example above (D2).

So, how would you need to change up the formula in order to compensate for time zone changes? Because there are many times when I cross time zones so the Dep and Arr times are reflective of local times and not total duration of the flight.

Have I confused you yet? Lol. Let me know if you might have a solution. I can email you the spreadsheet so you can possibly help me out. I truly appreciate it!!!

Tony