looking for help with a formula to solve this problem

[ktdre]

hi everyone,

i would really appreciate it if someone can help me sort this out please...

i have this machine that logs phone calls made on an internet telephone from the office... it puts them in an excel file... it showes in one column the duration of a call (in this format HH:MM:SS) and the cost total of that call in the column next to it... the billing for the calls is per minute and our boss wants the call durations to be a rounded time (i.e. if the call was 00:06:37 he wants to only see 00:07:00 or say 00:59:23 he wants to see 01:00:00) is there any way at all of doing this automated as there is a log of over 20000 calls over 12 months that i need to manually adjust if not...

any help would be greeeeaaatfullly appreciated...

thanks

KT

 

[erik.van.geit]

you're WELCOME

Good books are useful, but I cannot help much on books. I've never read a book about Excel, only one part of one book some months ago: using this forum + helpfiles.

This question has come up rather often. You can do a search on this forum. See the link"search" on top of the page.
most of the time when I need an advanced search I'm using
http://www.google.com/advanced_search?q=site:MrExcel.com

you can check out the recommended links and add-ins

kind regards,
Erik

 

[ktdre]

Thanks again erik...


i will take your advice....

gotta get up to pace with this stuff its soo usefulll


cheers again bud...


kt

 

[ktdre]

Hi again Erik,

I feel like killing my boss right now...

this is what he told me

he wants me to get the total minutes of a call into a cell NOT as a time format

i.e. say if the call duration was 00:37:43 he wants in the column next to it just the number 38

also if the call duration was say 01:32:33 then he wants in the column next to it just the number 93 i.e. total no. of minutes and the minutes rounded up.

i think they are going to use these figures for something else that i am not being told about....

when you get a chance let me know if this is at all possible coz i feel his IT man cannot do it and has sent me on mission impossible...

cheers buddy,

KT

 

[Aladin Akyurek]

Hi again Erik,

I feel like killing my boss right now...

this is what he told me

he wants me to get the total minutes of a call into a cell NOT as a time format

i.e. say if the call duration was 00:37:43 he wants in the column next to it just the number 38

also if the call duration was say 01:32:33 then he wants in the column next to it just the number 93 i.e. total no. of minutes and the minutes rounded up.

i think they are going to use these figures for something else that i am not being told about....

when you get a chance let me know if this is at all possible coz i feel his IT man cannot do it and has sent me on mission impossible...

cheers buddy,

KT

=ROUND(A2*24*60,0)

 

[ktdre]

Thanks a million aladdin.... works like a charm


cheers

KT

 

[erik.van.geit]

Thanks a million aladdin.... works like a charm


cheers

KT

you already recieved that in one of my first posts
since your boss wanted to round up, I gave you a formula with CEILING

unless, I'm missing something here

kind regards,
Erik

 

[ktdre]

hey guys... just wanted to swing in and say hey... the formulas worked perfectly... you dont know how greatfull i am for them... i used your formula Erik and then rounded it up to a whole no. with aladdins formula...

hope to keep hearing from you guys...


best wishes,
khalid