+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    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!
    Attached Files Attached Files
    Last edited by jrtaylor; 09-21-2020 at 01:22 PM.

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,083

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

    No attachment.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

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

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

  4. #4
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

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

    Why not something on row 3 like

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    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. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    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")
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    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. #8
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

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

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. TEXT date function giving strange results
    By QSGuy in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-16-2019, 06:01 PM
  2. Using ln() function produces strange results
    By OGAngryHulk in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-25-2018, 07:20 PM
  3. Strange NPV Results
    By windpowerfinance in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-02-2017, 05:05 AM
  4. Replies: 8
    Last Post: 06-07-2016, 11:28 AM
  5. [SOLVED] Strange vlookup results
    By Mary-Lou in forum Excel Programming / VBA / Macros
    Replies: 17
    Last Post: 06-01-2006, 10:50 PM
  6. [SOLVED] Strange results...
    By Jim May in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-24-2006, 05:30 PM
  7. Strange IF results
    By MWE in forum Excel General
    Replies: 0
    Last Post: 03-25-2005, 08:14 PM

Tags for this Thread

Bookmarks

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