Calculating time, I think

[steve astrop]

Hi, not posted for a while, stuck and hope someone knowledgeable can assist. I have created a spreadsheet to show how busy our car park is with shift patterns. I would like to highlight with conditional formatting the first and last entry where the total spaces exceed a specific number for that time. The formula below works (I think) for most shift patterns but when the shift starts before and ends after midnight it doesn't. The data is on the attached sheet Spaces_Data Tab. As I said, not posted for a long time, apologies if

=IF(B2<1,"",IF(OR(B1<$AA$2,B1>$AB$2,),IF(B6>50,TRUE,"1")))

Car park Calculations.xlsx
	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	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX	AY	AZ	BA	BB
1			00:00	00:30	01:00	01:30	02:00	02:30	03:00	03:30	04:00	04:30	05:00	05:30	06:00	06:30	07:00	07:30	08:00	08:30	09:00	09:30	10:00	10:30	11:00	11:30	12:00	12:30	13:00	13:30	14:00	14:30	15:00	15:30	16:00	16:30	17:00	17:30	18:00	18:30	19:00	19:30	20:00	20:30	21:00	21:30	22:00	22:30	23:00	23:30			Start Time	End Time
2	Shift 1	All Day	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2			00:00	23:59
3	Shift 2	Day														45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45	45											07:00	19:00
4	Shift 3	Night	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23	23																						23	23	23	23	23	23	23	23	23	23	23			19:00	07:00
Spaces Data

Cell Formulas
Range	Formula
B2:B4	B2	='Home page'!B4
C2:AX2	C2	=IF(MOD(1+'Spaces Data'!C1-'Home page'!$E$4,1)=MAX(MOD(1+'Spaces Data'!C1-'Home page'!$E$4,1),MOD(1+'Spaces Data'!C1-'Home page'!$F$4,1)),"",'Home page'!$I$4)
C3:AX3	C3	=IF(MOD(1+'Spaces Data'!C1-'Home page'!$E$5,1)=MAX(MOD(1+'Spaces Data'!C1-'Home page'!$E$5,1),MOD(1+'Spaces Data'!C1-'Home page'!$F$5,1)),"",'Home page'!$I$5)
C4:AX4	C4	=IF(MOD(1+'Spaces Data'!C1-'Home page'!$E6,1)=MAX(MOD(1+'Spaces Data'!C1-'Home page'!$E$6,1),MOD(1+'Spaces Data'!C1-'Home page'!$F$6,1)),"",'Home page'!$I$6)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C4:AX4	Expression	=IF(C4>1,IF(AND(OR(C1<$BA$4,C1>$BB$4,), C30>330),TRUE, ""),FALSE)	text	NO
C3:AX3	Expression	=IF(C3>1,IF(AND(OR(C1<$BA$3,C1>$BB$3,), C30>330),TRUE, ""),FALSE)	text	NO
C2:AX2	Expression	=IF(C2>1,IF(AND(OR(C1<$BA$2,C1>$BB$2,), C30>300),TRUE, ""),FALSE)	text	NO

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Calculating Time?
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.