Excel formula to allow display number of hours in particular periods of time

[Justyna82]

Hi

I have almost 30,000 rows spreadsheet and i am looking to calculate how many times and when the equipment was utilised. I am looking for formula that would allow me to assign start time and end time (column A and B) to one of the 4 periods of time and they are as follow: 00:00-06:00, 06:00-12:00, 12:00-18:00 and 18:00-00:00 (column CX, CY, CZ, DA). I would like to display the number of hours that occurred in that period of time. For example if start time is 00:00 and end time is 01:00 i would like 1 hr to appear under column CX 00:00-06:00. If the start time is 05:00 and end time is 19:00 i would like to display 1 hr under column CX (00:00-06:00), 6 hrs under column CY (06:00-12:00), 6 hrs under column CZ (12:00-18:00) and 1 hr under column DA (18:00-00:00). The column C to column CT calculates if there was any use of equiment in 15 minutes intervals. The column CU simply adding how many 15 minutes intervals there was, column CV calculates minutes and column CW change them to hours, not sure how helpful that is.

I had to hide some of the columns as the spreadsheet is too large to upload image and I am unable to add XL2bb, but i hope this make sense.

I would be extremely grateful if you can help.

Thank you

 

[myall_blues]

Try:

Book1
	A	B	C																																																																																																		CW	CX	CY	CZ	DA
1	Start time	End time																																																																																																				00:00-06:00	6:00-12:00	12:00-18:00	18:00-00:00
2	5:00	19:00																																																																																																				1.00	6.00	6.00	1.00
3	0:00	1:00																																																																																																				1.00	0.00	0.00	0.00
Sheet1

Cell Formulas
Range	Formula
CX2:CX3	CX2	=MAX((MIN(TIME(6,0,0),$B2)-MAX(TIME(0,0,0),$A2))*24,0)
CY2:CY3	CY2	=MAX((MIN(TIME(12,0,0),$B2)-MAX(TIME(6,0,0),$A2))*24,0)
CZ2:CZ3	CZ2	=MAX((MIN(TIME(18,0,0),$B2)-MAX(TIME(12,0,0),$A2))*24,0)
DA2:DA3	DA2	=MAX((MIN(TIME(23,59,59),$B2)-MAX(TIME(18,0,0),$A2))*24,0)