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.)
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.