Search code examples
excelvlookupexcel-2013

VLOOKUP not returning for datetime

I have a list of hours when water is used. this list does not generate hours when water was not used. I'll have list like:

Hour              Liters
5/3/14 6:00 PM    36.288
5/3/14 7:00 PM    15.328
5/3/14 8:00 PM    1.6
5/4/14 11:00 PM   18.752
5/5/14 12:00 AM   21.664
5/5/14 1:00 AM    21.76
5/5/14 2:00 AM    21.76

Now I want to put them in a serial format so it should be like:

5/3/14 6:00 PM   36.288
5/3/14 7:00 PM   15.328
5/3/14 8:00 PM   1.6
5/3/14 9:00 PM   0.0
5/3/14 10:00 PM  0.0 
5/3/14 11:00 PM  0.0
5/4/14 12:00 AM  21.664
5/4/14 1:00 AM   21.76
5/4/14 2:00 AM   21.76

I used VLOOKUP to retrieve for the date values and output the corresponding water used. It shows #N/A. I verified the date decimals using the match function and it gives me true.

Solution

  • Welcome to the world of time, base60 vs. base10 and 15 significant digit floating point precision. I find that sometimes it is simply expedient to give yourself a 'time window' to meet rather than try to round off ranges.

           Time Criteria

    The formula in E2 is,

    =SUMIFS($B$2:$B$8, $A$2:$A$8, ">"&D2-TIME(0, 0, 2), $A$2:$A$8, "<"&D2+TIME(0, 0, 2))

    This give a four (±2) second window to match rather than attempting an exact match on what could be a repeating decimal. That could conceivably be tightened further but is probably fine for your hourly data. Fill down as necessary.

    My column of 'serialized' times were generated using =A2 in D2 then =D2+TIME(1, 0, 0) in D3 and filled down.