Why does multiplying a custom h:mm formatted cell, by an rate/hour, then that by

Question

Why does multiplying a custom h:mm formatted cell, by an rate/hour, then that by 24 work?

I've got a spreadsheet that I'm using to track my hourly work on a project for billing. I'm tracking the time worked by entering it in the custom format of h:mm in the cells of a column. The summation works fine at the bottom of the column.

Then in another column I've got the hourly rate, let's say $30/hour.

In another cell I was multiplying the first data in the summation cell, by the hourly rate in the cell containing the hourly rate. The answer was way off. So I Binged it trying to find how to fix it, and I found some answers on various websites that said what I had to do was include in the multiplication I'd already done another term of 24. So if the summation cell was C20, and the cell that had the hourly rate was D20, and I wanted the answer to be in a third cell, let's call it E20, then the formula I had to put into E20 is:

C20*D20*24

This works perfectly. It gives me the answer I need. But I don't understand why it works. Why do you have to include multiplying by 24?

Explanation / Answer

Hi,

If you simply multiply the summed time by the rate you will get the wrong answer and to understand why you have to understand how times are stored in Excel.

Time is stored as a decimal. For example put 12:00 in a cell and format as general and you will see it is .5 or 1/2 a day, 6 PM would be .75. Now if the sum of your cells was 12:00 hours then that's .5 and what we really want is a decimal 12 hours and that where the 24 comes on because 0.5 * 24 = 12. 24 is used because there are 24 hours in a day.

We can take this a stage further, say your rate was paid by the second and the sum of our time was again 12:00. There are 86400 seconds in a day (24hrs*60mins*60seconds) so if we multiply our 12:00 * 86400 we convert that time to seconds.

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.