Time format setup

[Tamas]

Hi,

I got a big database of phone calls and I'd like to analyze call duration.

000:00:02:10 --- this is the format of the numbers I'm getting, this should be 2 minutes 10 seconds, but I can't really transform it to the right format to be able to use it for creating sums or averages.

Any idea how to transform?

Thanks,
Tamas

<colgroup><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

To change the actual value, we just need to remove the first 4 characters. Try doing it this way... select the column with your "time" values in it, then press CTRL+H to bring up the Replace dialog box, type 000: (that is, 3 zeroes followed by a colon) in the "Find what" field, leave the "Replace what" field empty, click the "Options>>" button and make sure the "Match entire cell contents" check box does not have a check mark in it, then click the "Replace All" button.

 

[Piktro]

What's your desired format?

 

[kevatarvind]

try below

Excel Workbook
	A	B
1	000:00:02:10	02:10
2	000:00:02:10	02:10
Sheet1

 

[Piktro]

If you are going to be doing this often.

Sub ReformatTime()
' ReformatTime Macro
' Keyboard Shortcut: Ctrl+Shift+T
    Cells.Replace What:="000:", Replacement:="", LookAt:=xlPart, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

 

[Rick Rothstein]

Here is another macro that you can consider...

Sub ReformatTime()
  Dim Addr As String
  Addr = "A2:A" & Cells(Rows.Count, "A").End(xlUp).Row
  Range(Addr) = Evaluate(Replace("IF(LEN(@),MID(@,5,99),"""")", "@", Addr))
End Sub

 

[Tamas]

Hi,

First of all, thanks for everybody for helping me - this is an amazing community - I'm new here, but I love it already.
I tried all, this date format is so tricky, they all work, but it still wont let me sum them up.

Finally the solution that worked was Rick Rothstein's macro - again, thanks for helping me, and thanks for the others as well!

try below
Sheet1

*	A	B
1	000:00:02:10	02:10
2	000:00:02:10	02:10

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:97px;"><col style="width:82px;"></colgroup><tbody>
</tbody>

Spreadsheet Formulas

Cell Formula B1 =TEXT(REPLACE(A1,1,SEARCH(":",A1),""),"MM:SS") B2 =TEXT(RIGHT(A1,8),"MM:SS") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4