Help with phone bill spreadsheet?

  chrishillcoat 12:12 09 Oct 2003
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 2003

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 2003

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 2003

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

  VoG II 12:55 09 Oct 2003

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

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

  chrishillcoat 13:29 09 Oct 2003

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)

Thanks for your help.

Chris

  stlucia 14:56 09 Oct 2003

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.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

8 brilliant character artists speaking at Pictoplasma 2018

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?