Adding Two Times Up In Excel

Question

Hi People

I I'm have small headache caused today by excel and the way it takes control of everything. I have two time values I need to add together then divide by a value I got charged for that time to work out how much it cost me an hour.

so say I was charged day on for 12:30:00

on day 2 I was charged for 13:20:00

and day 3 I was charged for 20:30:00

In total it cost me £1000

The calculation would be

£1000 / (12:30:00 + 13:20:00 + 20:30:00 = 46:20:00)

I would guess to get the answer I would

1000 / Sum(12:30:00+13:20:00+20:30:00) = price per hour

But it doesn't...

I guess I need some sort of convert function on the time or to divide it by 24 to get hours but I'm stuck

Help would be hugely appreciated!

Answer 1

Excel should be performing the calculation correctly, but it is just showing as a time. Format the result cell as "number" instead.

Answer 2

Excel represents times as fractional days, so your function is actually representing the rate per day.
Assuming your times are in cells A1:A3, you can adjust your function to something like:

1000 / Sum(A1:A3) / 24

which will give you the rate per hour (by my calculations, £21.58/hr).

As others have mentioned, you will need to set the cell to a currency format.

Answer 3

You can use the HOUR and MINUTE functions to get the hour and minute portions of the time values, however as lc mentions, you do need to ensure that the formatting of the cell is set to Number, as it will default to a time.

Answer 4

Assuming you have your hh:mm:ss values running down the page (column A), in the next column put the formula

=(A1-INT(A1))*24

underneath this you can put your formula

= 1000 / SUM(A1:A3)

but don't forget to format it as Number.