Excel Calculating Phone bill per second

[Mannypros]

Hi All,
A client of mine has been charged the wrong bill, so I need to manually do this however because the bill is calculated per second billing the excel formula am using is giving me the wrong answer am using A1*B1*24*60*60 does anyone know which formula I can use to re-calculate over 3500 calls made please the data is in format hh:mm:ss*0.03*24*60*60.

 

[Becky]

I'm not sure I follow what you are asking... what information does the bill give you, and what do you need to convert it to?

 

[Mannypros]

Hi Beckey, my client has been charged using our normal BT standard call charges and not the agreed 0.03ppersecond as agreed on their agreement so I need to work out the entire bill for local and national charges based on 0.03p per second billing.

 

[Mannypros]

I'm not sure I follow what you are asking... what information does the bill give you, and what do you need to convert it to?

Hi Becky please see the format and information I have from the bill. the sales price should be calculated based on 0.03p per second if you have a formula I can use other than *24*60*60 that will be great as it is giving me the wrong total.

Call Date Call Time Duration Description Time Band Salesprice
01/11/2016 11:39:29 00:23:14 London - Local Peak 1.9547
01/11/2016 13:27:30 00:00:03 London - Local Peak 0.1
01/11/2016 13:50:45 00:11:56 London - Local Peak 1.0507
01/11/2016 14:13:00 00:00:37 Southend-on-Sea Peak 0.1453
01/11/2016 14:26:41 00:21:48 London - Local Peak 1.84
01/11/2016 14:40:22 00:05:10 Southend-on-Sea Peak 0.5093
01/11/2016 14:44:39 00:03:50 Consett Peak 0.4027
01/11/2016 14:53:08 01:03:55 London - Local Peak 5.2093
01/11/2016 16:04:55 00:29:28 London - Local Peak 2.4533
01/11/2016 16:17:52 00:02:44 Portsmouth Peak 0.3147
01/11/2016 16:41:18 00:19:44 London - Local Peak 1.6747

 

[Becky]

I don't see why your initial suggestion wouldn't work (ie, call duration x 0.03 x 24 x 60 x 60), so I think the formula is fine. I think your issue may potentially be due to formatting - make sure that the call duration is formatted as h:mm:ss (you can find it in the Custom section of the formatting options) and make sure that the result of your formula is set to either 'number' or 'currency' (whichever you'd prefer).

If you're unsure how to format cells, all you need to do is right-click on them and select "Format cells..." from the drop down list.

Hope this helps!

 

[Mannypros]

Hi Becky, yes that is the formula I am using but rather than lower the bill it is increasing it. It went from being £1.97p for a call that lasted for 23mins as per our standard billing rate to £4.81 using the discounted rate

 

[Becky]

It's not clear from your posts above - are you trying to charge 0.03 pence or £0.03 (ie 3 pence)? That may be the error you're looking for.