Excel Time increment

[th081]

i have two tables, one table is small and i have entered a start date and time and a end date and time togeher with a lookup value eg 01/08/23 00:00, 01/08/23 04:30, Brand A.

The other table is large and i am incrementing a whole month minute by minute, eg

01/08/23 00:00, 01/08/23 00:01
01/08/23 00:01, 01/08/23 00:02

i am using index match to check if the dates and times in table 2 fall between the dates and times in table one and bring back the third column from table 1. The issue is for the very last minute it is not bringing back the value from table one i.e

01/08/23 04:29, 01/08/23 04:30 - it is saying when i do a = test that the 01/08/23 in table 2 is not the same as that in table 1. it seems when excel is incrementing them in table two the number diverge a bit.

Any tips, i have tried in incrementing in diff ways

 

[awoohaw]

How are you treating the Time Values? The last second of a day in Excel is 23:59:59. It thinks 00:00:00 is the first moment of the morning.
Not sure if that is the problem or not.

You may want to post a mini workbook of your data using the xl2bb add in (link below). The forum can debug better if you have some sample data. You need not post the huge tables just portions of the ones that can cover your scenarios.

Or you could put the work book on a file share.

Thanks in advance.

 

[th081]

This is table 2

	Event 1	01/07/2023 00:26	01/07/2023 00:27
	Event 1	01/07/2023 00:27	01/07/2023 00:28
	Event 1	01/07/2023 00:28	01/07/2023 00:29
	Error	01/07/2023 00:29	01/07/2023 00:30

with the formula is 01/07/23 00:29 as =F38+ "00:01:00"

this is table 1

Start Date and Time	End Date and Time	Event Name
01/07/2023 00:00​	01/07/2023 00:30​	Event 1
09/07/2023 02:00​	09/07/2023 02:30​	Event 2
09/07/2023 02:30​	09/07/2023 03:00​	Event 3

this is the formula that is doing the index match

=IFERROR(INDEX(C$4:C$200,MATCH(1,(F39>=A$4:A$200)*(F39<=B$4:B$200)*(G39>=A$4:A$200)*(G39<=B$4:B$200),0)),"Error")

as you can see it is not recognising the time in Roe 39 as falling into event 1

 

[awoohaw]

I think I have it working with your formula as well:

Book1
	A	B	C	D	E	F
36
37
38		Event 1	2023-01-07 00:26:00	2023-01-07 00:27:00
39		Event 1	2023-01-07 00:27:00	2023-01-07 00:28:00
40		Event 1	2023-01-07 00:28:00	2023-01-07 00:29:00
41		Event 1	2023-01-07 00:29:00	2023-01-07 00:30:00
42
43
44
45
46
47			Start Date and Time	End Date and Time	Event Name
48			2023-01-07 00:00:00	2023-01-07 00:30:00	Event 1
49			2023-09-07 02:00:00	2023-09-07 02:30:00	Event 2
50			2023-09-07 02:30:00	2023-09-07 03:00:00	Event 3
51
52
Sheet3

Cell Formulas
Range	Formula
B38:B41	B38	=IFERROR(INDEX($E$48:$E$50,MATCH(1,(C38>=$C$48:$C$50)*(C38<=$D$48:$D$50)*(D38>=$C$48:$C$50)*(D38<=$D$48:$D$50),0)),"Error")
C39:C41	C39	=C38+ "00:01:00"
D39:D41	D39	=D38+"00:01:00"

 

[th081]

have you changed anything in my formula, i am starting my date and time form 01/07/23 00:00 and adding a minute each time, its when it gets to 00:30 that i see the issues.

 

[th081]

looking at the formula it works if you start from minute 00:27, the 00:29 min is correctly picked up but if you start from 00:00 it is not, so there seems to be some sort of error being built up

 

[awoohaw]

I think it is the way TIME is calculated when adding vs when just showing the actual time:
Look at the decimal differences. By rounding the TIMES (both the look up value and the look up ranges) in the Match Formula I think you can get it to work.
You can't just round up the values because your adding rounded numbers and eventually that catches up in the calculation.

Book1
	A	B	C	D	E	F	G
1
2
3		Event 1	2023-01-07 00:00:00	2023-01-07 00:01:00
4		Event 1	2023-01-07 00:01:00	2023-01-07 00:02:00
5		Event 1	2023-01-07 00:02:00	2023-01-07 00:03:00
6		Event 1	2023-01-07 00:03:00	2023-01-07 00:04:00
7		Event 1	2023-01-07 00:04:00	2023-01-07 00:05:00
8		Event 1	2023-01-07 00:05:00	2023-01-07 00:06:00
9		Event 1	2023-01-07 00:06:00	2023-01-07 00:07:00
10		Event 1	2023-01-07 00:07:00	2023-01-07 00:08:00
11		Event 1	2023-01-07 00:08:00	2023-01-07 00:09:00
12		Event 1	2023-01-07 00:09:00	2023-01-07 00:10:00
13		Event 1	2023-01-07 00:10:00	2023-01-07 00:11:00
14		Event 1	2023-01-07 00:11:00	2023-01-07 00:12:00
15		Event 1	2023-01-07 00:12:00	2023-01-07 00:13:00
16		Event 1	2023-01-07 00:13:00	2023-01-07 00:14:00
17		Event 1	2023-01-07 00:14:00	2023-01-07 00:15:00
18		Event 1	2023-01-07 00:15:00	2023-01-07 00:16:00
19		Event 1	2023-01-07 00:16:00	2023-01-07 00:17:00
20		Event 1	2023-01-07 00:17:00	2023-01-07 00:18:00
21		Event 1	2023-01-07 00:18:00	2023-01-07 00:19:00
22		Event 1	2023-01-07 00:19:00	2023-01-07 00:20:00
23		Event 1	2023-01-07 00:20:00	2023-01-07 00:21:00
24		Event 1	2023-01-07 00:21:00	2023-01-07 00:22:00
25		Event 1	2023-01-07 00:22:00	2023-01-07 00:23:00
26		Event 1	2023-01-07 00:23:00	2023-01-07 00:24:00
27		Event 1	2023-01-07 00:24:00	2023-01-07 00:25:00
28		Event 1	2023-01-07 00:25:00	2023-01-07 00:26:00
29		Event 1	2023-01-07 00:26:00	2023-01-07 00:27:00
30		Event 1	2023-01-07 00:27:00	2023-01-07 00:28:00
31		Event 1	2023-01-07 00:28:00	2023-01-07 00:29:00
32		Event 1	2023-01-07 00:29:00	2023-01-07 00:30:00		=VALUE(D32)	44933.020833333400000000000000
33		Event 2	2023-01-07 00:30:00	2023-01-07 00:31:00
34		Event 2	2023-01-07 00:31:00	2023-01-07 00:32:00
35
36
37
38			Start Date and Time	End Date and Time	Event Name
39			2023-01-07 00:00:00	2023-01-07 00:30:00	Event 1	=VALUE(D39)	44933.020833333300000000000000
40			2023-01-07 00:30:00	2023-01-07 01:00:00	Event 2	=VALUE(D40)	44933.041666666600000000000000
41			2023-01-07 01:00:00	2023-01-07 01:30:00	Event 3	=VALUE(D41)	44933.062500000000000000000000
Sheet1

Cell Formulas
Range	Formula
B3:B34	B3	=IFERROR(INDEX($E$39:$E$41,MATCH(1, (ROUND(C3,6)>=ROUND($C$39:$C$41,6))* (ROUND(C3,6)<=ROUND($D$39:$D$41,6))* (ROUND(D3,6)>=ROUND($C$39:$C$41,6))* (ROUND(D3,6)<=ROUND($D$39:$D$41,6)),0)),"Error")
C4:D34	C4	=C3+"00:01:00"
F32,F39:F41	F32	=FORMULATEXT(G32)
G32,G39:G41	G32	=VALUE(D32)
C40:D41	C40	=C39+"00:30:00"

 

[Alex Blakenburg]

I am guessing you are getting a rounding error. Someone can probably give you a simpler version, but in the meantime give this a try:

20240126 Time Lookup th081.xlsx
	A	B	C	D	E	F	G
1	Table 1
2
3	Start Date and Time	End Date and Time	Event Name
4	1/07/2023 0:00	1/07/2023 0:30	Event 1
5	1/07/2023 2:00	1/07/2023 2:30	Event 2
6	1/07/2023 2:30	1/07/2023 3:00	Event 3
7
35
36
37					Table 2
38
39					Event 1	1/07/2023 0:26	1/07/2023 0:27
40					Event 1	1/07/2023 0:27	1/07/2023 0:28
41					Event 1	1/07/2023 0:28	1/07/2023 0:29
42					Event 1	1/07/2023 0:29	1/07/2023 0:30
43					Event 2	1/07/2023 2:00	1/07/2023 2:12
44					Event 3	1/07/2023 2:30	1/07/2023 2:35
Sheet3

Cell Formulas
Range	Formula
B4:B6	B4	=A4+TIME(0,30,0)
E39:E44	E39	=LET(rnd,24*60, tStart,ROUND($F39*rnd,0)/rnd, tEnd,ROUND($G39*rnd,0)/rnd, rngStart,ROUND($A$4:$A$200*rnd,0)/rnd, rngEnd,ROUND($B$4:$B$200*rnd,0)/rnd, rngEvent,$C$4:$C$200, IFERROR(INDEX(rngEvent,XMATCH(1,(tStart>=rngStart)*(tStart<=rngEnd)*(tEnd>=rngStart)*(tEnd<=rngEnd))),"Error"))

 

[th081]

Thank you Awoohaw - this seems to work.

Alex - thank you as well

 

[awoohaw]

Happy to help.

Best Wishes!