MS Excel Formula Help

[CommSecy]

I have a "time sheet" with 55 members working various hours. Right now, I am manually entering the numbers in green, which is labor-intensive. I want to add a formula that will add only the hours that fall between 1500 and 0700.
Any help would be very much appreciated.

Thank you.

 

[Dave Patton]

A concise example of your challenge can be posted with the forum's tool named XL2BB.

The following may help you.

Dates and Time 2024.xlsm
	A	B	C	D
1		Start	End	Time
2		15:00	07:00	16:00
3
4b

Cell Formulas
Range	Formula
D2	D2	=MOD(C2-B2,1)

 

[Dave Patton]

Dates and Time 2024.xlsm
	A	B	C	D	E
1		Start	End	Time	Time
2		19:00	07:00	12:00	12:00
3		19:00	07:00	12:00	12:00
4		19:00	07:00	12:00	12:00
5		22:00	07:00	09:00	09:00
6		14:00	23:00	09:00	09:00
7
4b

Cell Formulas
Range	Formula
D2:D6	D2	=MOD(C2-B2,1)
E2:E6	E2	=C2-B2+(B2>C2)

 

[CommSecy]

Dates and Time 2024.xlsm
	A	B	C	D	E
1		Start	End	Time	Time
2		19:00	07:00	12:00	12:00
3		19:00	07:00	12:00	12:00
4		19:00	07:00	12:00	12:00
5		22:00	07:00	09:00	09:00
6		14:00	23:00	09:00	09:00
7
4b

Cell Formulas
Range	Formula
D2:D6	D2	=MOD(C2-B2,1)
E2:E6	E2	=C2-B2+(B2>C2)

Thank you for your time & reply; however, the formula does not work as 14-23 should result in a total of 8hrs, not 9. Whatever the start and end times, I only want to add hours falling between 1500 and 0700 as that is the only hours that night differential pay applies. for instance, see examples below:
0700-1600 should result in a 1hr night diff
1300-2200 should result in 7hrs night diff

I am looking for a formula/workaround so that I do not have to manually input night diff hours if one exists.

I apologize if I did not initially explain my situation correctly. right now, I have a drop-down using the sumif formula to fill in all the different scenarios of night diff. I would like to avoid that if possible....

 

[Dave Patton]

Dates and Time 2024.xlsm
	A	B	C	D	E	F	G
1		Start	End	Time	Time	15:00	07:00
7
8		14:00	23:00	08:00	08:00
4b

Cell Formulas
Range	Formula
D8	D8	=MOD(C8-MAX(B8,F1),1)
E8	E8	=LET(t,MAX(B8,F1),C8-t+(t>C8))

 

[CommSecy]

Thank you for your help. Unfortunately, that does not work in every scenario.

 

[CommSecy]

If a formula exists to add ONLY hours that fall between 1500 and 0700 the next day, I would like to be able to add (get a total) of only the hours worked between 1500 and 0700, which falls into night diff pay. So regardless of what time someone starts or ends their tour I only want a total of hours worked between the hours of 1500 and 0700. Obviously, being a 24-hour department, I have a ridiculous number of combinations, so the formula would have to pull only the hours worked between 1500 and 0700. I do not know if this is even possible. If it is, it is certainly above my ability to work out. Thank you,

 

[CommSecy]

I found this and tried the formula above, and it's not working, but I think it should. What am I doing wrong?

Thank you!

 

[Dave Patton]

You can review this fragment that yields the number of hours.
You did not post an extract of your sheet that we can use.

Dates and Time 2024.xlsm
	C	D	E	F	G	H	I	J	K	L	M
1
2	10:00 PM	7:00 AM							09:00		7:00 AM
3											3:00 PM
4
4b

Cell Formulas
Range	Formula
K2	K2	=MOD(MIN(D2,M2)-MAX(C2,M3),1)

 

[CommSecy]

You can review this fragment that yields the number of hours.
You did not post an extract of your sheet that we can use.

Dates and Time 2024.xlsm
	C	D	E	F	G	H	I	J	K	L	M
1
2	10:00 PM	7:00 AM							09:00		7:00 AM
3											3:00 PM
4
4b

Cell Formulas
Range	Formula
K2	K2	=MOD(MIN(D2,M2)-MAX(C2,M3),1)

Thank you, I will try that now...