Please help solve... basic

[Fegal]

Hello
I have this formula looking at the sheet below it, i would expect a return in D23 of 18..
what am I doing wrong?

TIA!!

 

[awoohaw]

	Rates					James
Job	Builder	Apprentice			Column1	Start	Stop	Time	Job
Crawford	65	40		May	1/05/2023	07:30	17:00	9.00	Crawford
Crawford Extras	65	40		May	1/05/2023	07:30	17:00	9.00	Crawford
Auckland rd Lot 1	65	40		May	1/05/2023
Auckland Rd Lot 2	65	40		May	1/05/2023
Auckland Rd extras	65	40		May	2/05/2023
88 TeAwa Ave	65	40		May	2/05/2023
				May	2/05/2023
				May	3/05/2023
				May	3/05/2023
				May	3/05/2023
				May	4/05/2023

		JS	MT	1	2
Jan							0
Jan							0
Jan							0
Jan							0
Jan							0
Feb							0
Feb							0
Feb							0
Feb							0
Feb							0
Mar							0
Mar							0
Mar							0
Mar							0
Mar							0
Apr							0
Apr							0
Apr							0
Apr							0
Apr							0
May	Crawford	9.00	9.00	0.00	0.00		1
May							1
May							1
May							1
May							1

You have time values in the the date column in your lookup table. cell formatted as month with a value of (2023-5-1) does not equal cell formatted as month with a value of (2023-5-1 8:00 am)

 

[Peter_SSs]

With XL2BB Mini Sheet is better than Table Only. However, if that is causing a problem, what rangeshave you shown in the two ranges in post #9?

 

[awoohaw]

your month value in column F needs to be something like this: Date(Year(G7),Month(G7),1)
or G7-Day(G7)+1

 

[Fegal]

your month value in column F needs to be something like this: Date(Year(G7),Month(G7),1)
or G7-Day(G7)+1

ok. this was the answer... thank you both for your patience!

 

[awoohaw]

I'm happy you found a solution here.

Best Wishes!