Excel: Converting From Integer Parts Time To Minut

Question

I have some columns where the date values are in integer format, i.e.14.06736111 for 1:37. I want to get the format into minutes.

I tried using the [m[ format in custom format, and that works on many of the cells, but some of them throw out crazy numbers. For example, for the cell where the time is 1:37 I am getting 20257 instead of 97. It looks like the cells that have wacky values also have a nonsense date string attached (1/14/1900).

I can use find/replace to remove the date string and the the format works again, but that seems inefficient.

Is there a better way around this?

Answer 1

Using =A1-Int(A1) as suggested by Ken White helped a lot, but I was still having problems. Through a bit of trial and error I found that the best way to convert an fractal time value into whole minutes was to use the following formula:

=FLOOR(A1/0.000694, 1)

Answer 2

Your 14.06736111 is not a time; it's a date (01/14/1900) with a time (.06736111 of a 24-hour period). I'm guessing that the date portion is incorrect, or you're not actually seeing a date value at all.

To display just the time, your cell first needs to remove the integer part of the date (the part to the left of the decimal point). If your date value is in cell A1 as 14.06736111:

=A1-Int(A1)

@nutsch points out in a comment below that a shorter means of doing this would be

=MOD(A1, 1)

Set the cell formatting to Time:

1:37:00AM