driving me mad - why is this formula returning #N/A

[oldbrewer]

Start time	Finish time	Hours worked	Billable hours
Tuesday 29 August 18:00	Wednesday 30 August 07:00	13	20
Sunday 03 September 18:00	Monday 04 September 06:00	12	24
		is start day Sunday	before 8pm	after 8am	tot hours	reg hours	premium hours	prem rate	hours
29/08/2017 21:00	30/08/2017 05:00	no	0.00	0.00	8.00	0.00	8.00	1.5	12
29/08/2017 21:00	30/08/2017 06:00	no	0.00	0.00	9.00	0.00	9.00	1.5	13.5
29/08/2017 21:00	30/08/2017 07:00	no	0.00	0.00	10.00	0.00	10.00	1.5	15
29/08/2017 21:00	30/08/2017 08:00	no	0.00	0.00	11.00	0.00	11.00	1.5	16.5
29/08/2017 21:00	30/08/2017 09:00	no	0.00	1.00	12.00	1.00	11.00	1.5	17.5
29/08/2017 21:00	30/08/2017 10:00	no	0.00	2.00	13.00	2.00	11.00	1.5	18.5
29/08/2017 21:00	30/08/2017 11:00	no	0.00	3.00	14.00	3.00	11.00	1.5	19.5
		ignore all above here
			non Sunday working
		finish time
	this cell is B26	05:00	06:00	07:00	08:00	09:00	10:00	11:00
start	17:00	16.5	18	19.5	21	22	23	24
time	18:00	15.5	17	18.5	20	21	22	23
	19:00	14.5	16	17.5	19	20	21	22
	20:00	13.5	15	16.5	18	19	20	21
	21:00	12	13.5	15	16.5	17.5	18.5	19.5
	non Sunday
	start	end	hours
	18:00	07:00	#N/A
	#N/A formula is
	=OFFSET($B$26,MATCH(B38,B27:B31,0),MATCH(C38,C26:I26,0))
	why is this formula returning #N/A please ?

<colgroup><col><col><col><col><col><col><col><col><col span="3"></colgroup><tbody>
</tbody>

 

[JackDanIce]

Can't tell what the cell address are and how they relate to the diagram to see what values your formula is picking up.

Have you tried using Excel's Evaluate Formula feature (Formulas->Evaluate Formula) to see how the final value of "#N/A# is calculated? I find using this feature on nested formula usually allows me to identify where or what is causing the error output.

 

[Scott T]

It worked for me. Maybe one of your times is text instead of time?

 

[oldbrewer]

ty Scott