Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
    
What have you tried? –  Excellll Mar 22 '13 at 1:23

1 Answer 1

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

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.