# I'm getting strange results on a RIGHT() function

1. ## I'm getting strange results on a RIGHT() function

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!

2. ## Re: I'm getting strange results on a RIGHT() function

No attachment.

3. ## Re: I'm getting strange results on a RIGHT() function

TMS... sorry about that! I attached it now.

4. ## Re: I'm getting strange results on a RIGHT() function

Why not something on row 3 like

Formula:
`Please Login or Register  to view this content.`

5. ## Re: I'm getting strange results on a RIGHT() function

The trailing zeros don't actually exist which is why you are getting some "odd" results.
If you format col D as General you will see what is in the cell.
Try using
=(D3-INT(D3))*100

6. ## Re: I'm getting strange results on a RIGHT() function

I would probably have just used the HOUR() function (which returns the "hour of day" value buried inside of a date/time serial number). This will return an integer between 0 and 23 that represents the hour portion of the m/dd/yyyy h:mm:ss.000 serial number stored in the cell.

HOUR(A3) to return the hour portion of the date/time serial number.
Test if it is 23 and return the value in column B if it is: =IF(HOUR(A3)=23,B3,"n/a")

7. ## Re: I'm getting strange results on a RIGHT() function

Thanks everyone. All your solutions (or slight variations thereof) work, so I appreciate your help!

8. ## Re: I'm getting strange results on a RIGHT() function

Glad to help & thanks for the feedback.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1