Match Function for Time of Day

[warpedone]

I have about 40 pages of data - each containing a table with the date and time of day (in even numbered, military hours).

There is a corresponding cell on the main page for each data page. The user selects a time (cell C1) and date (cell A1) and each cell returns a value from it's respective data page.

I'm having trouble with the Time of Day. Here's my whole equation:

=INDEX(y01data,MATCH($C$1,'008Y01'!$B$5:$B$28,1),MATCH(VALUE($A$1),'008Y01'!$C$1:$AO$1,0))

The Time of Day match is where it's messed up:
MATCH($C$1,'008Y01'!$B$5:$B$28,1)
I have to use "1" as my match type because "0" returns a #N/A
In using "1", when I select a different time of day, it sometimes won't switch rows (when I switch from 1:00 to 2:00 or 16:00 to 17:00 nothing happens).

I think the Match function with the 1 as the match type is thinking that the row above the one I'm wanting is close enough and not going to the correct row.

Any ideas?

 

[warpedone]

OK...since time is represented as a decimal and 16 of my numbers don't end (.xxx33333333 repeating or .xxx666666 repeating) - I'm pretty sure that's why I can't use a match type of "1"

Any idea how I limit the number of decimal places (not only what I can see but also what excel uses to calculate)?

 

[warpedone]

Got it! Thanks, me.

 

[Chillz]

Got it! Thanks, me.

How did you fix this with the time....having the same problem.

 

[warpedone]

How did you fix this with the time....having the same problem.

Trying to figure out what the heck I was working on - that was six years ago. If I find it, I'll let you know. Don't hold your breath though.

 

[warpedone]

Here's what I wound up using that worked:

User selects dropdown date in A1 and dropdown time in C1
In AC1, I put the equation =C1*100

I then used the equation =INDEX(y01data,MATCH(VALUE($AC$1)+1,'008Y01'!$B$5:$B$28,1),MATCH(VALUE($A$1),'008011'!$C$1:$AO$1,0)+2)

For the time on sheet 008Y01 (column B), I used 0 for 0:00; 4.17 for 1:00; 8.33 for 2:00; etc. up to 95.833 for 23:00 (looks like it's 24 hours spread out over a range of 100).
Date runs across row 1 of 008Y01 from C1 to AO1 (that worked fine without having to manipulate it)

Been a long time and not sure why it works without looking at it a little harder, but, there it is.