Excel time format and calculations -- Ok, I don't get it

Nothing is as baffling as how excel handles time. I know it counts forward from 1900, etc. However, if I want to enter 2 minutes, 30 seconds into a cell formatted for time, nothing seems to work.

2.3 gives 12:00.0 or 12 minutes, zero seconds ... what?
2.5 (two and a half, maybe?) gives 00:00.0 ... what?
02:30 (I'll just use its syntax) gives 30:00.0 ... huh?

Then there's the calculations. Let's say it takes me an hour to make 150,000 units of something. I want to know how long it will take to make 789 units. So 789/150,000 in a cell formatted for time, excel tells me that will take 07:24.7 or seven minutes 25 seconds, but that's not right. That's not even close to right.

So, I don't get it. What's going on?

In addition to days being the number of elapsed days since 1st January 1900 (i.e. they are always integers), times are stored as fractions of a 24-hour day. So, 12 hours when shown in time format of 12:00:00 is actually stored as 0.5. Another thing to be aware of is if you format a cell as hh:mm:ss, then Excel will only show values between 0:00:00 and 23:59:59, so if you have, say, 37 hours then it will only show 13:00:00. To show it as 37:00:00 you need to format the cell as [hh]:mm:ss - the square brackets prevent Excel from wrapping the times at 24 hours.

So, if you take 789/150000 the answer is 0.00526 - that is the number of hours it takes. But if you put that fraction in a cell and format it as time, it will be interpreted as 0.00526 of a DAY, which is 0:07:34. To be consistent, you also need to divide by 24, which will give you 0:00:19, or 19 seconds.

Another approach would be not to format the cell as time - keep it as General and use this formula:

=789/150000*60*60

which will give you 18.94 (seconds).

Yes, it can be confusing, but hope this helps.

Pete

Thanks! Lot's of googling turned up lot's of long discursive discussions about the 1900 issue, but nothing this practical. Thanks again!