Hour, Minute, Second formatting in Pivot Table

brianfitz

New Member
Joined
Aug 6, 2010
Messages
18
Hi guys, hope you can help.

I have a sheet containing all of my pivot table data which is exported from our internal phone system. It contains stuff like handling time of calls, average call durations etc but the data is stored in seconds. For instance 8162 seconds is 6hrs 52 minutes. I get this figure by editing the cell formula to read =8162/60/60 and then changing the Cell Format to Custom and hh:mm:ss.

My question is I have over 2000 lines of this data, which increases by 2000 every month, and the above method of finding my hh:mm:ss is impractial to do as there dozens to do per line (meaning editing potenially 30,0000 individual cells.) is there a way that I can convert all of the time cells to this format and formula?

Sorry if I'm not being very clear but I appreciate any help you may be able to offer.

Many thanks,

Brian
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Welcome to the Board.

Sorry, no. You can't format a cell with decimal seconds as a time. You have to convert it to a serial time with a formula.

You could use a macro to do the conversion for you.
 

brianfitz

New Member
Joined
Aug 6, 2010
Messages
18
Thanks very much for your reply Andrew. As I don't really have much experience/success with macros could you recommened any resources which might point me in the right direction?
 

brianfitz

New Member
Joined
Aug 6, 2010
Messages
18

ADVERTISEMENT

Name of the sheet is sheet1 and the columns are D, E, F, G, I, J, K, L, M, N, P, R, Z, AA and columns AP and AQ are dates but I'm not sure this is relevant.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Try:

Code:
Sub Test()
    Dim LR As Long
    Dim r As Long
    With Worksheets("Sheet1")
        If .Range("D2").NumberFormat = "[hh]:mm:ss" Then
            MsgBox "Already converted!"
            Exit Sub
        End If
        LR = .Cells(.Rows.Count, 1).End(xlUp).Row
        For r = 2 To LR
            With .Range("D" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("E" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("F" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("G" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("I" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("J" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("K" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("L" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("M" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("N" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("P" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("R" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("Z" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
            With .Range("AA" & r)
                .Value = .Value / 3600
                .NumberFormat = "[hh]:mm:ss"
            End With
        Next r
    End With
End Sub
 

brianfitz

New Member
Joined
Aug 6, 2010
Messages
18

ADVERTISEMENT

Wow, thanks so much for that, I really appreciate it! it did seem to start off well enough however it did come up with an error, possibly because the data doesn't start in these columns until line 6? As in the first piece of timing data is D6.

It did try to run the macro and it did come up with timing data but in the empty cells at d1.

Any ideas?
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,640
Messages
5,512,566
Members
408,904
Latest member
Buttnoid02

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top