Microsoft Excel - time calculate to currency?

Question

Please can someone help me to calculate a total weekly time, because my total of 5 days comes to 21hrs which is incorrect. It should be 45hrs. One person logs in at 07:30 and logs out 16:30 this gives me 9hrs for that day. Now he works 5 days a week. End of the week he has 45 hrs but my excel gives me 21hrs. Now I want to convert this into currency at the end of the week.

Answer 1

Even though you have the cells formatted to display time, excel is actually looking it as a fraction of a day.

Lets say you have 7:30 in cell A1 and 16:30 in B1 and

=B1-A1 in C1 - which would show as 9:00, which appears to be correct.   But if you change the format to a general number format it will show 0.375.  This is the number of days (or fraction of a day) that was worked.  And of course over the course of 5 days it adds up to 1.875, which when displayed it the time format shows 21:00 (or 9pm).

The formula in cell C1 should be =(B1-A1)*24, which will give you 9 (the number of house worked).  Be sure you have cell C1 in a number format, instead of a date or time format.  

Hope this helps.

Answer 2

You are not adding the correct cells to your total hours cell.

There is no way you can have any combination of 9 hours that add up to 21 hours.