Formattting the date in a Pivot Chart

[hattrick_123a]

Hi There

Appreciate any help with this.

I have a pivot chart with the following date format:
6/11/2011

Now I want to change it into the following date format:
6/11/2011 0:00

To do this I get the following Macro after recording it

Sub Macro4()
'
' Macro4 Macro
'
'
    Range("A2").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields("Time").Name = _
        "d/mm/yyyy h:mm"
End Sub

But if I run this Macro(Macro4) it changes the PivotField “Time” to “d/mm/yyyy h:mm “, which is not what I want.

Can anyone please help me correct this?? See attached.




Building from this I have multiple sheets with Pivot Charts with the date format:

6/11/2011

I have created the following Macro that will go through every Pivot Chart in every Pivot Sheet and hopefully change it to the format I want:
6/11/2011 0:00

The Macro is below, but it is not doing what I want it to do. As I have copied the line from the recorded macro above.

Sub generic()
'' this goes through every worksheet


Dim ws As Worksheet
Dim objPT As PivotTable
Dim objPTField As PivotField
Dim objPTItem As PivotItem

For Each ws In Worksheets
ws.Visible = xlSheetVisible
MsgBox "WorkSheet: " & ws.Name & vbCrLf
    
    
    Range("A2").Select
           For Each objPT In ws.PivotTables
        MsgBox "PivotTable: " & objPT.Name & vbCrLf
         For Each objPTField In objPT.PivotFields
                     
objPT.PivotFields("Time").Name = _
        "d/mm/yyyy h:mm"

            Next
        Next objPT
Next ws

End Sub

So I think if I get this line of code right I will be all set.

objPT.PivotFields("Time").Name = _
        "d/mm/yyyy h:mm"

Thanks

[Miraun]

SO CLOSE!

    ActiveSheet.PivotTables("PivotTable2").PivotFields("Time").NumberFormat = "d/mm/yyyy h:mm"

[hattrick_123a]

Hey Miraun

Thanks for that. Just trying to understand why the macro did not record it the way you wrote it?

Also, is there a good place to find the various properties options available to this object(e.g. Pivot Field.NumberFormat)?? I mean should I be able to find this somewhere or does this come with experience?

Thanks Again