Time in Excel

[Chris in U.K.]

Hi folks,

Can anyone help me with this please? (MS Help completely incomprehensible)

How can I make Excel express a time (a quantity of minutes, not a clock time) as minutes and seconds?

This is to do with with working out target times in cycle racing - calculation gives, for example, 18.75 minutes. How can I make excel (excel 2002 running in XP pro) display this as 18 mins, 45 seconds??

Thanks,

Chris.

[Chris in U.K.]

I think I've figured it out for myself, having unscrambled a strangely-worded article on MSKB (which does, incidentally, contain a mistake)

I'll share the answer with you, in case it's keeping anyone else awake at night.

The thing is, when you apply a time format to a cell, XL assumes that the value in the cell is day-based.

The time format is hh:mm:ss

So, to make the time format work, if the number in the cell is in reality minutes and decimals of a minute, you have to turn it into a decimal of a day, by dividing by 24 and then by 60.
If your value is hours-based, just divide by 24, and if your value is seconds-based, divide by 24 then by 60 then by 60 again.

In my case this is 18.75mins/24/60 giving 0.0130208 days - now apply the time format and you get 0:18:45, or zero hrs, 18 mins and 45 secs, which is what I wanted.

You need to be careful because minutes and seconds are both in base 60 - if you just divide 18.75 by 24 and apply the format, you would get 18:45:00 which may look like 18mins, 45secs and zero decimals of a sec, but would actually be 18hrs, 45 mins and zero secs, which is the wrong answer - see what I mean?

I think I've got this right, and I hope I've explained it right!

HTH someone, somewhere, sometime.

Chris.


P.S. Apologies to Mr Gates - the example in the MSKB article doesn't work properly if you use the UK format menu, but it does if you use the USA one.

Chris.