• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

vlookup with time

C

cold

New Member
Hi,

Is there a way to use V Lookup with time as lookup value?

8:00:00 12

8:00:01 14

8:00:02 18

Thank you
 
Hi ,


Can you specify what kind of rounding is required , is it to the nearest hour ?


What will 8:31 round to ? 8:17 ? Please give details or more examples.


Narayan
 
@NARAYANK991


Hi!


What a peculiar way of rounding that you have at your place! Here we always round 8:31 to "eight and a bit"... And it's an universal and tautological method: it never fails!


Regards!


PS: if you want the general formula it's this (assuming your time is in A1):

=CONCATENATE(CHOOSE(MOD(HOUR(A1),12)+(1-SIGN(MOD(HOUR(A1),12)))*12,"one","two","three","four","five","six","seven","eight","nine","ten","eleven","twelve")," and a bit")
 
Hi,


Maybe rounding to nearest quarter?


=ROUND(A2*96,0)/96

or

=MROUND(A2, TIME(0,15,0))


Or rounding up or down to nearest quarter?


=CEILING(A2,"00:15")


=FLOOR(A2,"00:15")
 
Hi all,

I have a column with increasing time points

8:01:00

8:02:02

8:03:09

...

9:05:01

9:06:55...

I would like to round them down to the earliest and smaller time point:

8:00:00

8:00:00

8:00:00

...

9:00:00

9:00:00...

Thank you
 
As oldchippy suggested with the nearest quarter idea, formula would be:

=FLOOR(A2,"01:00")
 
Hi Cold,


If you just want to convert the time stamps to the earliest and smaller time point (round by hour) then this formula will do.


=TIME(HOUR(A1),0,0)

where A1 is cell where you have the time stamp.


If in your table data you round off the time, then the vlookup will give the first occurence of looked up value only, what are you trying to pull using look up?


Regards,

Prasad DN
 
Both ways are working. Thank you

In fact what I Ideally want to do is to do a vlookup on column A (time) and get the column B value BUT the vlookup value i'm chasing is always round time points (ex: 8:00:00, 9:00:00...). In the example below the vlookup value would be 8:00:00 and I wish to get 12. Thank you

8:02:00 12

8:05:01 14

8:07:02 18

...

9:01:04 44

9:04:55 99

...
 
To get 12, you would need to use TRUE as the final arguement in your VLOOKUP. I'm still confused as to what the overall layout of your reference table looks like, but hopefully it makes sense when the workbook is in front of you. =)
 
It is working only when the vlookup value is higher than the first time point. In the example just above I will get no result if the vlookup value is 8:00:00 but I will get 12 (desired valued) if the vlookup value is 8:03:00.

Is there a way that the vlookup (True arguement) searches higer values instead of lower?

Thank you
 
No, I'm afraid it won't work like that. Can you change your lookup table and shift the 2nd col up 1 row?

[pre]
Code: 
0       12
8:02:00 14
8:05:01 18
8:07:02 22
...
9:01:04 99
9:04:55 105
...
[/pre]
 
I could shift the column B but I'm afraid to get confuse in the long run. I will stick with =FLOOR(A2,"01:00") and add one extra column.

Thank you all
 
You must log in or register to reply here.
Back
Top