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]

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

 

[ktdre]

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

 

[ktdre]

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

 

[erik.van.geit]

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

 

[ktdre]

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

 

[erik.van.geit]

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

RANGE FORMULA (1st cell)
C2    =A2*B2
C3    =A3*B3*1440

[Table-It] version 06 by Erik Van Geit

greetings from Belgium !
Erik

 

[ktdre]

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

 

[erik.van.geit]

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

kt

no adjustment needed

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

 

[ktdre]

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