Night Shift Differential

The formula in your other thread does what you want.

http://www.excelforum.com/excel-gene...ate-cells.html

Data Range

	A	B	C
1	Shift Start	Shift End
2	22:00	6:00
3
4
5	In	Out	Total
6	5:52 PM	12:33 AM	2:33
7	1:08 AM	6:29 AM	4:52
8

Formula entered in C6:

=IF(AND(A6<>"",B6<>""),IF(A6<B6,MAX(MIN(2,B6)-MAX(A$2,A6),0)+MAX(MIN(B$2,B6)-MAX(0,A6),0),MAX(MIN(1.25,B6+1)-MAX(A$2,A6),0)+MAX((B$2-A6),0)),"")

Format as h:mm

Copy down as needed.

Originally Posted by Tony Valko

The formula in your other thread does what you want.

http://www.excelforum.com/excel-gene...ate-cells.html

Data Range

	A	B	C
1	Shift Start	Shift End
2	22:00	6:00
3
4
5	In	Out	Total
6	5:52 PM	12:33 AM	2:33
7	1:08 AM	6:29 AM	4:52
8

Formula entered in C6:

=IF(AND(A6<>"",B6<>""),IF(A6<B6,MAX(MIN(2,B6)-MAX(A$2,A6),0)+MAX(MIN(B$2,B6)-MAX(0,A6),0),MAX(MIN(1.25,B6+1)-MAX(A$2,A6),0)+MAX((B$2-A6),0)),"")

Format as h:mm

Copy down as needed.

That formula does not work for me. I created an entirely new spreadsheet with just that exact information, even in the same exact cells, and it doesn't come out correctly. I need to get rid of the 8:00.

Incorrect.jpg

Post a SMALL sample file and show us what results you expect.

20 rows worth of data is plenty.

OK, your time values are not true time values. They're TEXT values that look like time values.

Here's what I did to convert them into true time values...

First, I unmerged the cells in column B.

I selected the range B6:B25
Goto the Data tab>Text to Columns
Click Finish

Do the same thing for the range K6:25

That will sometimes convert TEXT numbers (time values) into numeric numbers. It worked in this case.

Then, I put the formula in L6 and copied down to L25.

The results are identical to those you entered in column M.

I made a slight tweak to the formula wrt testing for empty cells:

=IF(COUNT(B6,K6)<2,"",IF(B6<K6,MAX(MIN(2,K6)-MAX(A$2,B6),0)+MAX(MIN(B$2,K6)-MAX(0,B6),0),MAX(MIN(1.25,K6+1)-MAX(A$2,B6),0)+MAX((B$2-B6),0)))

Here's your file with this implemented.

I broke the problem down into separate parts: Total time, Regular Hours, Night Hours. I also entered the start time of regular hours 6:00 AM and the End of regular hours 10:00 PM in cells S2 and U2.
For Total Hours: Enter this in F2 and fill down. (choose whatever column you want for your actual data)

Formula:

Please Login or Register  to view this content.

For Reg Hours: Enter this in G2 and fill down

Formula:

Please Login or Register  to view this content.

For Night hours: Enter this in H2 and fill down

Formula:

Please Login or Register  to view this content.

You're welcome. Thanks for the feedback!