Excel Time increment

T

th081

Board Regular
Joined
Mar 26, 2006
Messages
98
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
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.
 
Upvote 0
This is table 2

Event 101/07/2023 00:2601/07/2023 00:27
Event 101/07/2023 00:2701/07/2023 00:28
Event 101/07/2023 00:2801/07/2023 00:29
Error01/07/2023 00:2901/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 TimeEnd Date and TimeEvent 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
 
Upvote 0
I think I have it working with your formula as well:

Book1
ABCDEF
36
37
38Event 12023-01-07 00:26:002023-01-07 00:27:00
39Event 12023-01-07 00:27:002023-01-07 00:28:00
40Event 12023-01-07 00:28:002023-01-07 00:29:00
41Event 12023-01-07 00:29:002023-01-07 00:30:00
42
43
44
45
46
47Start Date and TimeEnd Date and TimeEvent Name
482023-01-07 00:00:002023-01-07 00:30:00Event 1
492023-09-07 02:00:002023-09-07 02:30:00Event 2
502023-09-07 02:30:002023-09-07 03:00:00Event 3
51
52
Sheet3
Cell Formulas
RangeFormula
B38:B41B38=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:C41C39=C38+ "00:01:00"
D39:D41D39=D38+"00:01:00"
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
ABCDEFG
1
2
3Event 12023-01-07 00:00:002023-01-07 00:01:00
4Event 12023-01-07 00:01:002023-01-07 00:02:00
5Event 12023-01-07 00:02:002023-01-07 00:03:00
6Event 12023-01-07 00:03:002023-01-07 00:04:00
7Event 12023-01-07 00:04:002023-01-07 00:05:00
8Event 12023-01-07 00:05:002023-01-07 00:06:00
9Event 12023-01-07 00:06:002023-01-07 00:07:00
10Event 12023-01-07 00:07:002023-01-07 00:08:00
11Event 12023-01-07 00:08:002023-01-07 00:09:00
12Event 12023-01-07 00:09:002023-01-07 00:10:00
13Event 12023-01-07 00:10:002023-01-07 00:11:00
14Event 12023-01-07 00:11:002023-01-07 00:12:00
15Event 12023-01-07 00:12:002023-01-07 00:13:00
16Event 12023-01-07 00:13:002023-01-07 00:14:00
17Event 12023-01-07 00:14:002023-01-07 00:15:00
18Event 12023-01-07 00:15:002023-01-07 00:16:00
19Event 12023-01-07 00:16:002023-01-07 00:17:00
20Event 12023-01-07 00:17:002023-01-07 00:18:00
21Event 12023-01-07 00:18:002023-01-07 00:19:00
22Event 12023-01-07 00:19:002023-01-07 00:20:00
23Event 12023-01-07 00:20:002023-01-07 00:21:00
24Event 12023-01-07 00:21:002023-01-07 00:22:00
25Event 12023-01-07 00:22:002023-01-07 00:23:00
26Event 12023-01-07 00:23:002023-01-07 00:24:00
27Event 12023-01-07 00:24:002023-01-07 00:25:00
28Event 12023-01-07 00:25:002023-01-07 00:26:00
29Event 12023-01-07 00:26:002023-01-07 00:27:00
30Event 12023-01-07 00:27:002023-01-07 00:28:00
31Event 12023-01-07 00:28:002023-01-07 00:29:00
32Event 12023-01-07 00:29:002023-01-07 00:30:00=VALUE(D32)44933.020833333400000000000000
33Event 22023-01-07 00:30:002023-01-07 00:31:00
34Event 22023-01-07 00:31:002023-01-07 00:32:00
35
36
37
38Start Date and TimeEnd Date and TimeEvent Name
392023-01-07 00:00:002023-01-07 00:30:00Event 1=VALUE(D39)44933.020833333300000000000000
402023-01-07 00:30:002023-01-07 01:00:00Event 2=VALUE(D40)44933.041666666600000000000000
412023-01-07 01:00:002023-01-07 01:30:00Event 3=VALUE(D41)44933.062500000000000000000000
Sheet1
Cell Formulas
RangeFormula
B3:B34B3=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:D34C4=C3+"00:01:00"
F32,F39:F41F32=FORMULATEXT(G32)
G32,G39:G41G32=VALUE(D32)
C40:D41C40=C39+"00:30:00"
 
Upvote 0
Solution
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
ABCDEFG
1Table 1
2
3Start Date and TimeEnd Date and TimeEvent Name
41/07/2023 0:001/07/2023 0:30Event 1
51/07/2023 2:001/07/2023 2:30Event 2
61/07/2023 2:301/07/2023 3:00Event 3
7
35
36
37Table 2
38
39Event 11/07/2023 0:261/07/2023 0:27
40Event 11/07/2023 0:271/07/2023 0:28
41Event 11/07/2023 0:281/07/2023 0:29
42Event 11/07/2023 0:291/07/2023 0:30
43Event 21/07/2023 2:001/07/2023 2:12
44Event 31/07/2023 2:301/07/2023 2:35
Sheet3
Cell Formulas
RangeFormula
B4:B6B4=A4+TIME(0,30,0)
E39:E44E39=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"))
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
316
dshtesy
D
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
65
cooleomter
C
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
64
Bernie Galve
B
S
  • Solved
Finding first, second, third matches
Replies
4
Views
487
ExceLoki
ExceLoki
E
  • Question Question
Pulling together data from multiple MS Forms spreadsheets and displaying into a single league table
Replies
6
Views
329
excelnewbie999
E

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top