Excel Calculating Phone bill per second


Joined
Jan 11, 2017
Messages
5
Reaction score
0
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.
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,510
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?
 
Joined
Jan 11, 2017
Messages
5
Reaction score
0
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.
 
Joined
Jan 11, 2017
Messages
5
Reaction score
0
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

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,510
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!
 
Joined
Jan 11, 2017
Messages
5
Reaction score
0
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
 
Ad

Advertisements

Becky

Webmistress
Joined
Mar 25, 2003
Messages
7,424
Reaction score
1,510
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top