Calculate number of sessions based on time

I get this, see formulas in column H:

Values as displayed

	A	B	C	D	E	F	G	H
1	After	Up to	Hours per PA		Shift	Start	Finish	PAs
2	7:00	19:00	4		Day	9:00	17:00	2
3	19:00	7:00	3		Late	17:00	22:00	1.5

Underlying formulas

	A	B	C	D	E	F	G	H
1	After	Up to	Hours per PA		Shift	Start	Finish	PAs
2	0.291666666666667	0.791666666666667	4		Day	0.375	0.708333333333333	=(1/4)*24*(MIN($B$2,G2)-MAX(F2,$A$2))+(1/3)*24*MAX(0,(MIN($B$3+1,G2+IF(G2<F2,1,0))-(MAX(F2,$A$3))))
3	0.791666666666667	0.291666666666667	3		Late	0.708333333333333	0.916666666666667	=(1/4)*24*(MIN($B$2,G3)-MAX(F3,$A$2))+(1/3)*24*MAX(0,(MIN($B$3+1,G3+IF(G3<F3,1,0))-(MAX(F3,$A$3))))

I didn't do much testing, just the two samples you gave. If you hit a snag, post back to this thread.

If a member helped you solve your problem, consider adding to their reputation by clicking addreputationiconsmall.jpg below their name.