# Help with phone bill spreadsheet?

chrishillcoat 12:12 09 Oct 03
Locked

Hi,

I'm trying to tabulate my company's phone bills into a spreadsheet. I've got each phone line in a column with the different tariffs (local, national...) next to it, and then for each quarter (ie. bill) I want to have a calculation of the volume of calls multiplied by the tariff to show how much we should be paying, next to the actual bill.

So my actual question (sorry for rambling!) is: how can I work out 35:10:53 x 2.69ppm? (Having more than 24 hours seems to be too much for Excel to cope with.)

Many thanks,
Chris Hillcoat

tbh72 12:41 09 Oct 03

Not the answer but the means to a solution, try and convert the quantity of hours into minutes eg 35 hours + 10 minutes + 53 seconds = (total minutes * .0269) = £56.76

stlucia 12:45 09 Oct 03

I would do it step by step -- someone else might give you a single magic formula.

But my way would be to create an "hours" column containing the first two characters (using the LEFT function) of the time (that would be "35" in your example), then create a "dummy" column containing the right five characters (using the RIGHT function) of the time (that would be "10:53" in your example). Then create a "minutes" column containing the first two characters of the "dummy" column (using the LEFT function) and a "seconds" column containing the last two characters of the "dummy" column(using the RIGHT function) -- they would then be "10" and "53" using your example.

The cost would then by (("hours"*60)+"minutes"+("seconds"/60))*ppm.

tbh72 12:54 09 Oct 03

Thank's Stlucia, that's what I said but in my typical LAZY manor!!!!

VoG II 12:55 09 Oct 03

Or with the time in A1, and pence per minute in B1

=B1*(60*HOUR(A1)+MINUTE(A1))

chrishillcoat 13:29 09 Oct 03

Thanks everyone: I used Stlucia's method but managed to combine everything into one cell:

=((((LEFT(S4,2)*60)+MID(S4,4,2)+(RIGHT(S4,2)/60))*D4)/100)

Chris

stlucia 14:56 09 Oct 03

You're welcome, tbh72. At least you got a response out in 4 minutes less than it took me to type mine!!

This thread is now locked and can not be replied to.

Nintendo Switch (Nintendo NX) release date, price, specs and preview trailer: Codename NX console…

1995-2015: How technology has changed the world in 20 years

8 things designers (and brands) need to know about the modern woman

How to speed up a slow Mac: 19 great tips to make an iMac, MacBook or Mac mini run faster | Speed…