Breakdown hours worked

[tezza]

Hi all,

Can anyone help automate the contents below please?

Col A & B are entered manually (well, copied from another data source) and the rest needs a formula/conditional formatting.

The idea is quite straight forward but I can't get excel to play ball.

If any of the hours from Col A and Col B are between 8pm and 9am then return how many hours are in that range, then the same for hours between 9am and 8pm. I think the biggest challenge would be row 6 so maybe only D6 and E6 needs to be correct and the rest would just follow.

The conditional format isn't so important but it would finish it off with a good visual

I've made a start with Col C

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD
1	Start Time	End time	Total Time	Between 8pm and 9am	Between 9am and 8pm		00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00
2	06:00	10:00	04:00	03:00	01:00
3	18:00	02:00	08:00	06:00	02:00
4	11:00	15:00	04:00	00:00	04:00
5	23:00	05:00	06:00	06:00	00:00
6	18:00	10:00	16:00	11:00	05:00
7	MANUAL ENTRY		FORMULATED ENTRY				CONDITIONAL FORMAT
Sheet1

Cell Formulas
Range	Formula
C2:C6	C2	=B2-A2+(B2<A2)

I've you take this on then I thank you very much in advance as I have roughly 1000 rows per week to calculate and it's taking a long time to do something I'm sure excel can do very quickly.

Tez

 

[tezza]

After a bit of research I think I've found a way to make it work, it might be crude but it's a start at least:

Timescales.xlsx
	A	B	C	D	E
1	Start Time	End time	Total Time	8am - 9pm	9pm - 8am
2	06:00	10:00	04:00	1:00	3:00
3	18:00	02:00	08:00	2:00	6:00
4	00:00	03:00	03:00	0:00	3:00
5	08:00	10:00	02:00	1:00	1:00
6	20:00	23:00	03:00	0:00	3:00
7	11:00	15:00	04:00	4:00	0:00
8	23:00	05:00	06:00	0:00	6:00
9	18:00	10:00	16:00	3:00	13:00
Sheet1 (2)

Cell Formulas
Range	Formula
C2:C9	C2	=MOD(B2-A2,1)
D2:D9	D2	=IF(A2<B2,MAX(0,MIN(B2,upper)-MAX(A2,lower)),MAX(0,upper-A2)+MAX(0,B2-lower))
E2:E9	E2	=C2-D2

Named Ranges
Name	Refers To	Cells
lower	='Sheet1 (2)'!$AG$2	D2:D9
upper	='Sheet1 (2)'!$AG$3	D2:D9

Timescales.xlsx
	AF	AG
1	Time scale
2	lower	09:00
3	upper	20:00
Sheet1 (2)

00:00	01:00	02:00	03:00	04:00	05:00	06:00	07:00	08:00	09:00	10:00	11:00	12:00	13:00	14:00	15:00	16:00	17:00	18:00	19:00	20:00	21:00	22:00	23:00

I couldn't put it side-by-side with this post due to an error message with Xl2bb on the following:

Conditional formatting G2:AD9
=AND($B2<$A2,$B2>G$1)
=AND(G$1>=$A2,OR(G$1<$B2+($B2<$A2),G$1<$B2))