Status
Not open for further replies.
1 - 10 of 10 Posts

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

#### OBP

·
##### 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.

#### Anne Troy

·
##### Registered
Joined
·
11,836 Posts
I think we need to know how it's displayed, Gadgetman, and the format of your cell.

#### Ratboy

·
##### 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 · ·

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

#### OBP

·
##### Registered
Joined
·
19,932 Posts
Did you try ratboy's formula in column D instead, it looks good?

·
##### Registered
Joined
·
393 Posts
Discussion Starter · ·
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.

#### OBP

·
##### 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 · ·
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.

#### OBP

·
##### 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.