MATCH formula

[MazurK]

Using: using Microsoft® Excel® for Microsoft 365 MSO (Version 2111 Build 16.0.14701.20240) 32-bit

I cannot get this formula to work, I get an N/A in the field. I have created a calendar on another worksheet, sample below.

Any help would be greatly appreciated.

Formula in Column F: =MATCH(E6,'16x5 Calendar'!C$4:C$3000,0)+(MATCH(E6,'16x5 Calendar'!C$4:C$3000,0)=MATCH(E6,'16x5 Calendar'!D$4:D$3000,0))

 

[Toadstool]

Hi Mazurk,

It is possible that rounding issues are causing a mismatch. Try doing a =VALUE(ref) of E6 and C3 showing 9 decimal places. That should give you a clue as to where you'll need to use ROUND.

 

[Eric W]

That's a rather strange looking formula. What are you trying to accomplish? What values do you want to see in F through I?

 

[MazurK]

That's a rather strange looking formula. What are you trying to accomplish? What values do you want to see in F through I?

Column G: =@INDEX('16x5 Calendar'!E:E,F6,1)-(@INDEX('16x5 Calendar'!D:D,F6,1)-MAX(INDEX('16x5 Calendar'!C:C,F6,1),E6))
Column H: =MATCH(G6+D6,'16x5 Calendar'!E:E)+1
Column I: =@INDEX('16x5 Calendar'!D:D,H6,1)-@INDEX('16x5 Calendar'!E:E,H6,1)+G6+D6

I am trying to use a 16x5 calendar to create my production schedule. i.e. job 1 has 12 hours run time, Job 2 has 8 hours. I want to know the end time of each job between the hours of 7:00 am and 11:30 pm. 11:30 pm to 7:00 am are non working hours.

 

[Eric W]

I'm still a bit hazy, but maybe:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1												Hours	Day	Begin	End	Cum Days
2		Start of first job:		1/31/2022 7:00
3												16.5	Mon	1/31/22 7:00:00 AM	1/31/22 11:30:00 PM	0.6875
4	Seq	Jobs	Hours	Days	Start				Stop			16.5	Tue	2/1/22 7:00:00 AM	2/1/22 11:30:00 PM	1.375
5												16.5	Wed	2/2/22 7:00:00 AM	2/2/22 11:30:00 PM	2.0625
6	1	Job A	5.87	0.244583333	1/31/22 7:00:00 AM				1/31/22 12:52:12 PM			16.5	Thu	2/3/22 7:00:00 AM	2/3/22 11:30:00 PM	2.75
7	2	Job B	4	0.166666667	1/31/22 12:52:12 PM				1/31/22 4:52:12 PM			16.5	Fri	2/4/22 7:00:00 AM	2/4/22 11:30:00 PM	3.4375
8	3	Job C	12	0.5	1/31/22 4:52:12 PM				2/1/22 12:22:12 PM			16.5	Mon	2/7/22 7:00:00 AM	2/7/22 11:30:00 PM	4.125
9	4	Job D	10.5	0.4375	2/1/22 12:22:12 PM				2/1/22 10:52:12 PM			16.5	Tue	2/8/22 7:00:00 AM	2/8/22 11:30:00 PM	4.8125
10	5	Job E	1	0.041666667	2/1/22 10:52:12 PM				2/2/22 7:22:12 AM			16.5	Wed	2/9/22 7:00:00 AM	2/9/22 11:30:00 PM	5.5
11	6	Job F	19	0.791666667	2/2/22 7:22:12 AM				2/3/22 9:52:12 AM
12
Sheet24

Cell Formulas
Range	Formula
D6:D11	D6	=C6/24
E6	E6	=D2
E7:E11	E7	=I6
P3:P10	P3	=P2+L3/24
I6:I11	I6	=IF(E6+D6>VLOOKUP(E6,$N$3:$O$10,2),INDEX($N$4:$N$11,MATCH(E6,$N$3:$N$10))+E6+D6-VLOOKUP(E6,$N$3:$O$10,2),E6+D6)

 

[MazurK]

Thank you.

If I need to add additional hours for when the next job would start, how do I keep that between the hours of 7:00 am and 11:30 pm. For example if E10 is delayed by 8 hours starting?

 

[Eric W]

My first inclination would be to put in another column with the idle time in it. Would this idle time use the off hours? For example, if the previous job ended at 11:00 PM, and there was 10 idle hours, would the next job start at 4:30 PM, or at 9:00 AM?

 

[MazurK]

My first inclination would be to put in another column with the idle time in it. Would this idle time use the off hours? For example, if the previous job ended at 11:00 PM, and there was 10 idle hours, would the next job start at 4:30 PM, or at 9:00 AM?

4:30 pm

 

[Eric W]

In that case, add columns F and G which are a duplicate of columns C and D, they just have the idle time in them instead of the job time. Then change E6+D6 to E6+D6+G6 everywhere in the formula.