I have a large workbook which obtains hourly data from a mysql database, then transforms the datestamp in that data into a number which Excel recognizes. The data I pull from the database is hourly, but I'm only using the data from the last hour of the day for doing some calculations. So my intention is to pull the data at 23:00 hours and ignore the rest.
My solution was to convert the database info into the Excel convention, which divides a day into base 10. (I think I'm correct here), in which 23:00 = .95 and 00:00 = .00.
(1/24 of a day = .041667, therefore 23:00 would be 23 x .041667, which = .95833. I then ROUNDDOWN this to .95).
Therefore if a cell in column E = .95, I want to capture the value in column B.
If you look at the attached worksheet, notice that cell E31 is returning a "95" rather than a "00". What's strange is that this is the first time in the worksheet that the formula is doing this (the complete workbook, the first misbehaving cell occurs in row 18,046). Am I making a mistake which I haven't detected?
If you want to suggest another formula for extracting the hour from the timestamp in column A, that's also fine. Thanks!
Bookmarks