Sum of minutes and seconds times price?

[John]

Just wondering if you can help as I am having a little trouble with my
formula.

I am trying to sum a phone call of 7 minutes and 44 seconds time by 35
pence.

Cell B21 I have 00:05:41 hh:mm:ss
Cell B22 I have 00:02:03 hh:mm:ss
Cell B23 I have 00:07:44 formula is =SUM(B21:B22) hh:mm:ss
Cell B24 I have £0.35 (35 pence in currency format)
Cell B25 I have £0.00 formula is =SUM(B23*B24)

Does anyone know what I am doing wrong on the last bit? I believe it
should return answer of £2.71. Is it a problem with the formula or the
format of the cell? I always stumble trying to calculate time by price
in Excell.

Thanks,
John

 

[Xt]

Just wondering if you can help as I am having a little trouble with my
formula.

I am trying to sum a phone call of 7 minutes and 44 seconds time by 35
pence.

Cell B21 I have 00:05:41 hh:mm:ss
Cell B22 I have 00:02:03 hh:mm:ss
Cell B23 I have 00:07:44 formula is =SUM(B21:B22) hh:mm:ss
Cell B24 I have £0.35 (35 pence in currency format)
Cell B25 I have £0.00 formula is =SUM(B23*B24)

Does anyone know what I am doing wrong on the last bit? I believe it
should return answer of £2.71. Is it a problem with the formula or the
format of the cell? I always stumble trying to calculate time by price
in Excell.

Thanks,
John

Your problem is that Excel stores times as fractions of a day, even
though it diaplays them as h/m/s. Just multiply by 60*24 to convert
days to minutes and you get the answer 2.71 Also you don't need the
SUM bit. B25=B23*B24*60*24

xt


xt

 

[joeu2004]

I am trying to sum a phone call of 7 minutes and 44 seconds
time by 35 pence.
Cell B21 I have 00:05:41 hh:mm:ss
Cell B22 I have 00:02:03 hh:mm:ss
Cell B23 I have 00:07:44 formula is =SUM(B21:B22) hh:mm:ss
Cell B24 I have £0.35 (35 pence in currency format)
Cell B25 I have £0.00 formula is =SUM(B23*B24)
Does anyone know what I am doing wrong on the last bit?

At a minimum, B25 should be:

=B24*B23*1440

Multiply by 1440 because there 1440 minutes in a day and Excel stores time
as a fraction of a day. So 1 hr is 1/24, 1 min is 1/1440, and 1 sec is
1/86400.

However, you probably want to round your computation to the pence. So B25
should be:

=ROUND(B24*B23*1440,2)

Finally, most phone services round usage up to the minute, at least here in
the US. So you might want the following in B25:

=ROUND(B24*ROUNDUP(B23*1440,0),2)