Excel - Mins - Hrs Mins Formula Problem

Excel Excel 2007
January 17, 2011 at 14:17:39
Specs: Windows Vista
Number of mins in A1

Using formula =INT(A1)/60)&":"&MOD(A1,60)

Some rsults are fine. However, some are not

eg 2400 mins shows 40:00
but later 2400 mins shows 40:4.54747350886464E-13

Format of cell A1 is Number to no dec places
Format of formula result cells is number to 2 dec places

I've tried format painter to ensure that is not the issue but cannot work this outout


See More: Excel - Mins - Hrs Mins Formula Problem

Report •

#1
January 17, 2011 at 15:16:14
re: "Using formula =INT(A1)/60)&":"&MOD(A1,60)"

I doubt it, since that formula won't work.

Excel won't even accept it because you have an extra parenthesis in it.

Either the ) after the first 60 has to be removed or the ) after the first A1 has to be removed.

Depending on which ) you remove, you'll get different results, so there's no sense in us trying to help until you tell us what formula you are really using.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 17, 2011 at 22:40:04
Apologies for the typo. I have removed the parenthesis

Number of mins in A1

Using formula =INT(A1/60)&":"&MOD(A1,60)

Some results are fine. However, some are not

eg 2400 mins shows 40:00
but later 2400 mins shows 40:4.54747350886464E-13

Format of cell A1 is Number to no dec places
Format of formula result cells is number to 2 dec places

I've tried format painter to ensure that is not the issue but cannot work this outout


Report •

#3
January 18, 2011 at 03:47:15
How is 2400 getting into A1? If it is a calculated value my guess would be that it is not always exactly 2400even if it appears to be.

2400.00000000001 returns 40:1.00044417195022E-11

Have you tried using the TEXT function to force the 2 digits?

This will return 40:00 for 2400.00000000001

=INT(A1/60)&":"&TEXT(MOD(A1,60),"00")

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •
Related Solutions


Ask Question