Have Geek Brain

Add two times to Excel

I have a little headache caused by superiority today and the way he controls everything. I have two time values ​​that I need to add together, then divide by the value I got during that time to figure out how much it cost me an hour.

so I will say that I was accused on the day 12:30:00

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

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

In total it cost me £ 1000

The calculation will be

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

I would guess to get the answer I would like

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

But it is not...

I am guessing that I need some kind of conversion at a point in time or dividing it by 24 to get the hours, but I am stuck

Help would be hugely appreciated!

0


a source to share


4 answers


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

1000 / Sum(A1:A3) / 24


which will give you a course per hour (according to my calculations, £ 21.58 / hour).

As mentioned, you need to set the cell in currency format.

+4


a source


Excel should do the calculations correctly, but it just displays as time. Format the result cell as "number".



0


a source


You can use the HOUR and MINUTE functions to get the hour and minute parts of time values, however, as indicated in lc, you need to make sure to format the cell to Number as it will default to time.

0


a source


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

=(A1-INT(A1))*24

under this you can put your formula

= 1000 / SUM(A1:A3)

but don't forget to format it as Number.

0


a source






More articles:


All Articles