# INT conversion of time is giving me an erroneous result

1. ## 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

2. ## 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. ## 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

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

Originally Posted by FearNix
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.

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