# looking for help with a formula to solve this problem

#### ktdre

##### New Member
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

### Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Hi, ktdre
Welcome to the Board !!!!!

if your data are in column A
in column B put formula
=CEILING(A1*1440,1)
(which is in fact =CEILING(A1*24*60,1) )
this will return integers (format as number)

if you want to get timeformatted cells as result, you can use
=CEILING(A1,1/1440)

kind regards,
Erik

Hi Erik,

thanks for the help... im gonna go give it a shot... i don't use excel in my daily life and i got stuck... im a telecommunication advisor for the company i work in... i hope its ok that im asking most probably a basic question to all you guys here....

if i face problems would it be possible for me to keep asking in this thread or would it anoy people here generally?

thanks soo much again and for the warm welcome to the forum...

Khalid

ok ... please dont kill me but... how can i add the formula? i thought it would be in the menus or in the right click drop down menu...

the data in column a is in a time format already and i want it to be in the same format in column b but with the secounds rounded up to a whole minute....

so should i use the last formula you listed above?

kt

Hi,

one of my friends - long time not seen - is working for telecommunicationcompany Mobistar here in Belgium. His name is Khalid

Feel welcome to post followup-questions in this thread. Other members might answer: anyway I'm often checking "my" threads, but understand that not each answer will be as quick...

best regards,
Erik

ohh cool.. im currently in Oman in the gulf... worked in ireland before but never in belgium...

thanks though for the help... i am working arround in excel to try and figure out as much as i can....

if i can help in any way with my field please do let me know...

best wishes,
khalid

ok ... please dont kill me but... how can i add the formula? i thought it would be in the menus or in the right click drop down menu...

the data in column a is in a time format already and i want it to be in the same format in column b but with the secounds rounded up to a whole minute....

so should i use the last formula you listed above?

kt
AS you can see, I wrote my previous reply before seeing yours.
Yes, use the last formula.
put it in cell B1 and copy down as needed

take into acount that if you want to use the result to calculate you will need to multiply by 1440, since what you see in the cell is considered as part of a day.
example
01:00:00 is counted as 1/24
06:00:00 is counted as (1/24)*6 = 1/4 of a day
00:01:00 is counted as 1/1440 of a day

in this table C2 has a wrong result
C3 is the correct amount to pay for 1 minute
A       B    C
1 time    rate price
2 0:01:00 0,20 0,00014
3 0:01:00 0,20 0,20

main

[Table-It] version 06 by Erik Van Geit
Code:
``````RANGE FORMULA (1st cell)
C2    =A2*B2
C3    =A3*B3*1440

[Table-It] version 06 by Erik Van Geit``````

greetings from Belgium !
Erik

GREAT GOT IT TO WORK!

Thanks alot...

it worked on the one cell selected (A1)

if i wanted to do multiple cells at once (say all the cells in column A) how do i adjust the formula?

kt

if i wanted to do multiple cells at once (say all the cells in column A) how do i adjust the formula?

kt

I wrote before
put it in cell B1 and copy down as needed

do some research on relative and absolute references
example
write =\$A\$1 in B1
copy down: each cell will have the same formule
that's absolute reference
now write = A1
copy down: in B2 you will see =A2
just what you need

copy can be made different ways
for you this trick would be ideal
click B1 (or cell with your formula)
doubleclick the little rectangle at the bottomright of your cell
DONE

Erik your a friend for life man...

thank you soo much for your wisdom... i was googling it and found what you just said

http://www-static.cc.gatech.edu/classes/AY2001/cs1321_summer/labs/lab4/lab4.html

thanks soo much again... if i can be of any assistance any day please dont think twice... i just finished the whole thing and i thought i was going to be infront of my pc editing them one by one for the next 3 months!!!

be well for now...

i am definitly going to learn loads more about this stuff... unbelievable how powerfull it is...

any advice on a good book or site to read up from?

kt

Replies
5
Views
401
Replies
1
Views
302
Replies
8
Views
670
Replies
1
Views
348
Replies
9
Views
1K

1,219,006
Messages
6,145,708
Members
450,635
Latest member
Rookie3510

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back