+ Reply to Thread
Results 1 to 4 of 4

INT conversion of time is giving me an erroneous result

  1. #1
    Registered User
    Join Date
    03-18-2020
    Location
    Reading, UK
    MS-Off Ver
    16 - 32bit
    Posts
    3

    Question INT conversion of time is giving me an erroneous result

    Hi,

    I just started a new workbook, and it's in very early stages. I have three middle-sheet columns which are: START TIME, END TIME and MINUTES

    It is a measure of minutes spent on an activity which is named at the beginning of that row.

    Anyway, I have formatted the time columns (1 and 2) as short time.

    I have formatted column 3 as Number.

    My first example had START: 02:00:00, and END 02:03:00.

    Before I used INT, and changed the formatting of column 3, I got the result from =INT((E3-D3)*1440) as 00:03:00

    All good.

    But when I changed the format to number with no decimal places, it gave me 2?!

    So I changed column 2 to 02:02:00, and my result becomes 1!!

    Then, I changed column 3 formatting back to time and then back to number, but this time with default 2 decimal places, and I get the same result... for 3 minutes, you get 2 and for 2 minutes you get 1.

    Next, I tried other numbers from 4 through to an hour and I got just what I wanted: 4 through 60!! And for completeness I tried 0 (columns 1 and 2 equal), and got 0!!

    I tried again, 2 and 3 minutes, and always get 1 less than I want!

    I haven't tested decimals between 0 and 4, but may do while waiting for reply/help... lol

    Is this a bug? Obviously I can input some checks that if the time is 2 or 3 hen add 1, but with this advanced version of Excel I shouldn't be doing this!

    Also, if this is a bug gone under the radar, how many spreadsheets out there have minor errors that may be compounded through complexity?!?

    Can anyone help? Am I doing something wrong? Should this be reported?

    FearNix
    Last edited by FearNix; 03-18-2020 at 11:15 PM.

  2. #2
    Registered User
    Join Date
    03-18-2020
    Location
    Reading, UK
    MS-Off Ver
    16 - 32bit
    Posts
    3

    Re: INT conversion of time is giving me an erroneous result

    Quick update:

    I completed that row, and started on the 2nd row which had:

    Col1: 02:04:00
    Col2: 02:10:00

    But the result in Col 3: 5!!!!!

    If you change the 10 in Col2 to 11, Col 3 result: 6
    If you change the 11 in Col2 to 12, Col 3 result: 7
    If you change the 12 in Col2 to 13, Col 3 result: 8
    If you change the 13 in Col2 to 14, Col 3 result: 9
    If you change the 14 in Col2 to 15, Col 3 result: 11 !?!?!

    I'm not sure what's going on...

    FearNix

  3. #3
    Registered User
    Join Date
    03-18-2020
    Location
    Reading, UK
    MS-Off Ver
    16 - 32bit
    Posts
    3

    Re: INT conversion of time is giving me an erroneous result

    Attaching this simple beginning as I haven't really got far. It will eventually have sheets/tabs for each casino or poker site, and will have more columns when I get to grips with more information as I start gathering.

    Hope it helps.

    FearNix
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 2010
    Posts
    1,398

    Re: INT conversion of time is giving me an erroneous result

    Quote Originally Posted by FearNix View Post
    I completed that row, and started on the 2nd row which had:

    Col1: 02:04:00
    Col2: 02:10:00

    But the result in Col 3: 5!!!!!
    Just because you see the time difference as 6 minutes does not mean that is how Excel sees it. Time in Excel (and VBA as well) is a fraction of a 24-hour day. Rarely is that fraction exact. In the case above, the actual calculated answer is 5.999999999999976 which Excel "helpfully" rounds to 6 when it attempts to show it with 10 digits, however the INT function is actually chopping off everything after the decimal point for the actual underlying value, not the displayed value. As with most calculations involving floating point number, it is better to use the ROUND function rather than the INT function. If you change your formula to this...

    =ROUND((B1-A1)*1440,0)

    You will consistently get the values you expect.

+ 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. Recorded macro not giving the same result as the by-hand result
    By dcwm in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-11-2019, 09:42 AM
  2. Get result from giving condition
    By HugoC in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 03-03-2019, 11:23 AM
  3. [SOLVED] Populating a date field from another workbook is giving an erroneous date
    By pongmeister in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-14-2017, 11:19 AM
  4. [SOLVED] Simple =Sum(B2:L2) result is not giving accurate result and =IMSUM(M2,O2) also.
    By SimonsGE in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 06-29-2013, 01:56 AM
  5. Formula giving different result on two different pcs
    By mdooley in forum Excel General
    Replies: 2
    Last Post: 05-28-2012, 07:05 PM
  6. Vlookup always giving n/a as result
    By ankit_85 in forum Excel General
    Replies: 4
    Last Post: 09-11-2010, 01:28 AM
  7. Formula not giving me the right result
    By Steved in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-09-2006, 10:25 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