Problem with Vlookup and time values 1

[ADE6]

Hi

I am using the formula below to look for times in column BE and then use the lookup table to assign a number from 1-6 that equates to four hour blocks of time.

The formula works ok, apart from when 00:00:00 times are involved and then i get a #N/A error message,based on the lookup table below 00:00:00 times should return the number 1.


=VLOOKUP(BE4,$BN$3:$BO$26,2,FALSE)





BN-------BO
00:00:00 1
23:00:00 6
22:00:00 6
21:00:00 6
20:00:00 6
19:00:00 5
18:00:00 5
17:00:00 5
16:00:00 5
15:00:00 4
14:00:00 4
13:00:00 4
12:00:00 4
11:00:00 3
10:00:00 3
09:00:00 3
08:00:00 3
07:00:00 2
06:00:00 2
05:00:00 2
04:00:00 2
03:00:00 1
02:00:00 1
01:00:00 1

Thanks for the ideas

Ade

 

[SkipVought]

Funny, I dont get an error.

I put 0 in BE4 and your formula returns 1.

What VALUE in BE4 returns NA?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[SkipVought]

I found your problem.

Please refer to my last posting to you where I needlessly expounded on the SEQUENCE of the TIME VALUES (at no extra charge ;-) ).

Your FIRST Time VALUE should be 1 and NOT 0.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ADE6]

Hi

Column BE(sample) shows the times in the format below,every time works with the Vlookup formula except 00:00:00


BE
03:00:00
02:00:00
01:00:00
00:00:00
23:00:00
22:00:00

Thanks Skip

Ade

 

[SkipVought]

I am not sure of the INTENT of your lookup range.

It seems to me that the ZERO Time Value ought to be at the BOTTOM of the list, since the corresponding Column BO value is 1.


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[ADE6]

You are right,it probably would be right to put 00:00:00 at the bottom of the lookup table,this is just the way it ended up.

I take you mean use the 0/24 method

So values >=0/24<4/24 would produce the number 1,equating to the first four hours of the day.


Thanks Skip

 

[ADE6]

SOLVED

I used =ISNUMBER(BO3) and found the cell that wasn't formatted correctly

Thanks again for the advice Skip

Ade