Puzzeld by result

[mole999]

I have rota conversion tool (the plan changed so I ditched IF in favour of LOOKUP and INDEX methods, both provide unexpected results)

Excel Workbook
	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
33	06:00	12:00		0600-1200	120	240	6:00		6:00
34	06:00	12:30		0600-1230	150	270	6:30	30	6:30	6:00
35	06:00	13:00		0600-1300	150	270	7:00	30	7:00	6:30
36	06:00	13:30		0600-1330	210	330	7:30	30	7:30	7:00
37	06:00	14:00		0600-1400	150	330	8:00	30	8:00	7:30
38	06:00	14:30		0600-1430	150	330	8:30	30	8:30	8:00
39	06:00	15:00		0600-1500	210	390	9:00	30	9:00	8:30
40	06:00	15:30		0600-1530	240	420	9:30	30	9:30	9:00
41	06:00	16:00		0600-1600	240	420	10:00	45	10:00	9:15
42	06:00	16:30		0600-1630	270	450	10:30	45	10:30	9:45
43	06:00	17:00		0600-1700	270	450	11:00	45	11:00	10:15
44	06:00	17:30		0600-1730	270	450	11:30	45	11:30	10:45
45	06:00	18:00		0600-1800	270	450	12:00	45	12:00	11:15
Test

For a reason I have not been able to determine the 06-13 shift and 06-14:30 both produce the wrong value and I can't see what must be plainly obvious

Excel Workbook
	A	B	C	D	E	F	G
1						Start	Fin
2	6:00	120	240			2	4
3	6:30	150	270			2.5	4.5
4	7:00	180	300			3	5
5	7:30	210	330			3.5	5.5
6	8:00	150	330			2.5	5.5
7	8:30	180	360			3	6
8	9:00	210	390			3.5	6.5
9	9:30	240	420			4	7
10	10:00	240	420			4	7
11	10:30	270	450			4.5	7.5
12	11:00	270	450			4.5	7.5
13	11:30	270	450			4.5	7.5
14	12:00	270	450			4.5	7.5
Times

 

[wideboydixon]

I don't fully understand what you're trying to achieve but I suspect your issue is down to using MATCH() with times which are fractional values. You might consider changing your Times table to use minutes at which point you could use an exact match. I re-worked your sheets like this:

Book1
	A	B	C	D	E	F	G
1						Start	Fin
2	360	120	240			2	4
3	390	150	270			2.5	4.5
4	420	180	300			3	5
5	450	210	330			3.5	5.5
6	480	150	330			2.5	5.5
7	510	180	360			3	6
8	540	210	390			3.5	6.5
9	570	240	420			4	7
10	600	240	420			4	7
11	630	270	450			4.5	7.5
12	660	270	450			4.5	7.5
13	690	270	450			4.5	7.5
14	720	270	450			4.5	7.5
Times

Cell Formulas
Range	Formula
F2	=B2/60
G2	=C2/60

Book1
	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
33	06:00	12:00		0600-1200	120	240	360		6:00
34	06:00	12:30		0600-1230	150	270	390	30	6:30	6:00
35	06:00	13:00		0600-1300	180	300	420	30	7:00	6:30
36	06:00	13:30		0600-1330	210	330	450	30	7:30	7:00
37	06:00	14:00		0600-1400	150	330	480	30	8:00	7:30
38	06:00	14:30		0600-1430	180	360	510	30	8:30	8:00
39	06:00	15:00		0600-1500	210	390	540	30	9:00	8:30
40	06:00	15:30		0600-1530	240	420	570	30	9:30	9:00
41	06:00	16:00		0600-1600	240	420	600	30	10:00	9:30
42	06:00	16:30		0600-1630	270	450	630	45	10:30	9:45
43	06:00	17:00		0600-1700	270	450	660	45	11:00	10:15
44	06:00	17:30		0600-1730	270	450	690	45	11:30	10:45
45	06:00	18:00		0600-1800	270	450	720	45	12:00	11:15
Sheet2

Cell Formulas
Range	Formula
AK33	=LEFT(AN33,2)&":"&MID(AN33,3,2)
AL33	=MID(AN33,6,2)&":"&MID(AN33,8,2)
AO33	=INDEX(Times!$B$1:$B$14,(MATCH($AQ33,Times!$A$1:$A$14,0)))
AP33	=INDEX(Times!$C$1:$C$14,(MATCH(AQ33,Times!$A$1:$A$14,0)))
AQ33	=MID(AN33,6,2)*60+MID(AN33,8,2)-MID(AN33,1,2)*60-MID(AN33,3,2)
AR33	=IF(AK33="","",IF(AQ33<=360,"",IF(AQ33<=600,30,45)))
AS33	=IF(AQ33=0,"",AQ33/1440)
AT33	=IF(AR33="","",AS33-AR33/1440)

WBD

 

[mole999]

Thanks WBD That's what I needed. I had tried pushing the times down and up, but wasn't getting a match