Calculating Phone bill per second

Excel Discussion in 'Microsoft Office' started by Mannypros, Jan 11, 2017.

  1. Mannypros

    Mannypros

    Joined:
    Jan 11, 2017
    Likes Received:
    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.
     
    Mannypros, Jan 11, 2017
    #1
    1. Advertisements

  2. Mannypros

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    974
    Location:
    Manchester
    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?
     
    Becky, Jan 11, 2017
    #2
    1. Advertisements

  3. Mannypros

    Mannypros

    Joined:
    Jan 11, 2017
    Likes Received:
    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.
     
    Mannypros, Jan 16, 2017
    #3
  4. Mannypros

    Mannypros

    Joined:
    Jan 11, 2017
    Likes Received:
    0
    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
     
    Mannypros, Jan 16, 2017
    #4
  5. Mannypros

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    974
    Location:
    Manchester
    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!
     
    Becky, Jan 16, 2017
    #5
  6. Mannypros

    Mannypros

    Joined:
    Jan 11, 2017
    Likes Received:
    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
     
    Mannypros, Jan 16, 2017
    #6
  7. Mannypros

    Becky Webmistress Administrator

    Joined:
    Mar 25, 2003
    Likes Received:
    974
    Location:
    Manchester
    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.
     
    Becky, Jan 17, 2017
    #7
    1. Advertisements

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 (here). After that, you can post your question and our members will help you out.
Similar Threads
  1. omanly

    Outlook Outlook displays wrong phone area code

    omanly, Sep 13, 2005, in forum: Microsoft Office
    Replies:
    0
    Views:
    445
    omanly
    Sep 13, 2005
  2. Deborah Mackenzie

    Outlook Search by contact phone numbers in outlook

    Deborah Mackenzie, Oct 8, 2008, in forum: Microsoft Office
    Replies:
    5
    Views:
    4,529
    Adywebb
    Oct 27, 2008
  3. Quanda
    Replies:
    1
    Views:
    1,587
    _PennyPunto
    Jun 22, 2009
  4. lal
    Replies:
    0
    Views:
    524
  5. tsmcneill

    Access Calculating UPH in MS Access

    tsmcneill, Sep 21, 2010, in forum: Microsoft Office
    Replies:
    0
    Views:
    1,703
    tsmcneill
    Sep 21, 2010
  6. hanswehr

    Excel Excel calculate pay based on rate per minute

    hanswehr, Apr 15, 2015, in forum: Microsoft Office
    Replies:
    7
    Views:
    1,626
    Becky
    Apr 20, 2015
  7. alan clayson

    Access access and windows phone

    alan clayson, Jul 2, 2015, in forum: Microsoft Office
    Replies:
    0
    Views:
    335
    alan clayson
    Jul 2, 2015
  8. ocs

    Access finding rank as per the class and stream

    ocs, Dec 27, 2016, in forum: Microsoft Office
    Replies:
    3
    Views:
    163
    Becky
    Dec 27, 2016
Loading...