Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

This is a sample of my input:

sample input data

Then, I have midnights in a consequent days and I would like to achieve this result automatically:

enter image description here

To put it in words: I have datetimes in one column and I would like to find the last datetime which is before midnight of a day and first datetime which is after this midnight. There can be none, one or more than one datetime for one day. I also need to find a value which is in a column next to the original input.

FYI: input data are snapshots of my gasmeter. Number in Value column is a total cubic meters spent before time in first column. I need to find those border values to calculate spending in a day. I am able to do the calculation, but I cannot manage to find those values in the input.

Thank you!

share|improve this question
up vote 2 down vote accepted

You will want to use the MATCH function.  It is like HLOOKUP and VLOOKUP except it returns an index (1..n) rather than a value (and if you aren’t familiar with HLOOKUP and VLOOKUP, you should probably learn about them).  MATCH(D2, $A$2:$A$14) will return the index of the last value in $A$2:$A$14 that is less than or equal to D2; i.e., (the index of) the last time before the midnight time in D2.  (This is in the form of an integer between 1 and 13, which may be a little confusing, but it’s all good.)  To avoid redundancy, it’s useful to use “helper columns”; so I put =MATCH(D2, $A$2:$A$14) into I2.  Then enter these formulae:

  • E2  →  =INDEX($A$2:$B$14, $I2, 1)
  • F2  →  =INDEX($A$2:$B$14, $I2, 2)
  • G2  →  =INDEX($A$2:$B$14, $I2+1, 1)
  • H2  →  =INDEX($A$2:$B$14, $I2+1, 2)

where we use INDEX to fetch the desired values from columns A and B.

I cleaned up your input a little, for testing/demonstration purposes:

                                                                Columns A and B

and here are the results:

                                Columns D through I

This will blow up for dates in Column D that are before A2.  To fix that, add another helper column (J) and change G and H:

  • J2  →  =IFERROR(I2, 1) + 1
  • G2  →  =INDEX($A$2:$B$14, $J2, 1)
  • H2  →  =INDEX($A$2:$B$14, $J2, 2)
share|improve this answer
    
Thank you, this did the trick. Now that I see the solution, it doesn't look so complex. Thx – jakubka Mar 23 '13 at 23:35

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .