Tech Support Guy banner
Status
Not open for further replies.
1 - 10 of 10 Posts

·
Registered
Joined
·
393 Posts
Discussion Starter · #1 ·
Could someone please give me the formula of converting the number of seconds placed in a cell to read minutes and seconds?
 

·
Registered
Joined
·
19,932 Posts
You can do this if you split the formula betwen two new cells.
If the first cell In Column D contans 75 seconds the second cell in Column E contains the formula =INT(D9/60) to give the Minutes the third cell in Column F contains the Formula =D9-E9*60 for the Seconds.
 

·
Registered
Joined
·
1,333 Posts
I'm sure Dreamboat will come up with a cleaner method, but his one will work. It's assuming the seconds are in cell A1.

=TIMEVALUE(ROUNDDOWN(A1/3600,0)&":"&ROUNDDOWN(MOD(A1,3600)/60,0)&":"&MOD(A1,60))
 

·
Registered
Joined
·
393 Posts
Discussion Starter · #5 ·
Thanks for your replies.

My worksheet is as follows, with the help of OBP using his formula:

"You can do this if you split the formula betwen two new cells.
If the first cell In Column C contans 75 seconds the second cell in Column D contains the formula =INT(C9/60) to give the Minutes the third cell in Column E contains the Formula =C9-D9*60 for the Seconds."

A= Date
B= Time of Call
C= Duration of Phone Call in seconds (Entered)
D= Minutes (Calculated)
E= Seconds (Calculated)


My worksheet would like to be as follows:

A= Date
B= Time of Call
C= Duration in Seconds (Entered)
D= The Duration in Minutes and Seconds (Calculated)
E= Running total of Hours, Minutes and Seconds (Calculated)

Thank you
 

·
Registered
Joined
·
393 Posts
Discussion Starter · #7 ·
I tried Ratboys formula, but it doesn't work.

I have used OBP's in columns D and E which does work...but I would like a running total of hours , minutes and seconds in columns F, G and H.
 

·
Registered
Joined
·
19,932 Posts
In F place this - =INT((SUM($B$2:$B2)/3600))
In G place this - =INT(MOD(SUM($B$2:$B2),3600)/60)
In H place this - =MOD(SUM($B$2:$B2),3600)-D2*60
 

·
Registered
Joined
·
393 Posts
Discussion Starter · #9 ·
OBP, is B correct in your formula?

I input the seconds in column C, in D is the formula =INT(C4/60) for the minutes, and in E is =C4-D4*60 for the seconds.
 

·
Registered
Joined
·
19,932 Posts
Gadgetman, You said in post #5 that you had the phone call time in seconds in column "B" so I used that in the formulae. It should be wherever you have the call time.
 
1 - 10 of 10 Posts
Status
Not open for further replies.
Top