Correcting Time Format

[jfezell]

I’m running a report on the average hold time of telephone calls. The data I extract for the report exports like this:

3:09
:00
:40
3:48
2:18
:12
:30
1:10
:43
:52
1:32
2:07
:14
:00

I need to re-format the cells to show like this:

3:09
0:00
0:40
3:48
2:18
0:12
0:30
1:10
0:43
0:52
1:32
2:07
0:14
0:00

If I only had 15 or 20, I would change them manually. But, this report has about 200 rows. And, I have 7 different reports to run.

I’m lost when it comes to making macros and using Visual Basic. Although, I can follow instructions and copy/paste. The code mentioned in this thread:

http://www.excelforum.com/excel-gene...s-to-time.html

Sub TransformTimp_2()
Dim mcell As Range
For Each mcell In Selection
If Len(mcell) = 6 Then mcell = 0 + "0" & mcell
mcell.NumberFormat = "[hh]:mm:ss "
Next mcell
End Sub

Doesn’t work with this particular format. It’s changing the data to this:

03:09:00
:00
:40
03:48:00
02:18:00
:12
:30
01:10:00
:43
:52
01:32:00
02:07:00
:14
:00

How can I change the VB code to give me what I want?

Thank you.

[Olly]

Based on the example data you post, try:

Sub foo()
Dim c As Range
For Each c In Selection
    c.NumberFormat = "hh:mm"
    If Left(c.Value, 1) = ":" Then
        c.Value = TimeValue("0" & c.Value)
    End If
Next c
End Sub

Select the range to transform, run the macro...

[Norie]

Try this.

Sub FixTime()
Dim rng As Range
Dim Res As Variant

    Set rng = Range("A1", Range("A" & Rows.Count).End(xlUp))
    
    With rng
        Res = Evaluate("INDEX(IF(LEFT(" & .Address & ")="":"", TIMEVALUE(""0""&" & .Address & ")," & .Address & "),,1)")
        .Value = Res
        .NumberFormat = "hh:mm"
    End With
    
End Sub

[jfezell]

Thanks Olly and Norie. Olly, your code did the trick - somewhat. It changed this:

1:21
:50
:10
:51
:57
2:12
1:20
3:52
1:18
1:41
2:03
1:53
:55
2:58
:44
:36
:45

To this:

1:21
00:50
00:10
00:51
00:57
2:12
1:20
3:52
1:18
1:41
2:03
1:53
00:55
2:58
00:44
00:36
00:45

I need the cells with minutes (ex: 1:21) to have a leading zero as well (ex: 01:21).

In other words, look like this:

01:21
00:50
00:10
00:51
00:57
02:12
01:20
03:52
01:18
01:41
02:03
01:53
00:55
02:58
00:44
00:36
00:45

How can I make that happen?

[Olly]

Okay, guessing your current values are stored as text. That's the problem with posting values in your post, rather than attaching a workbook, it means I have to guess at how your data is formatted...!

Try:

Sub foo()
Dim c As Range
For Each c In Selection
    c.NumberFormat = "hh:mm"
    If Left(c.Value, 1) = ":" Then
        c.Value = TimeValue("0" & c.Text)
    Else
        c.Value = TimeValue(c.Text)
    End If
Next c
End Sub

[jfezell]

[SOLVED] Correcting Time Format

I wasn't sure if it was okay for me to attach a spread sheet. Yes, you are correct. I was pasting into cells preformatted as TEXT. And, your updated VB code worked. I can now sort by time High/Low and the results are correct. I truly appreciate your help - you and Norie. Reputation added for both. Joe

[Norie]

jfezell

Just wondering, did the code I posted not work?

It worked for me with the sample data you posted.