Results 1 to 3 of 3

date issue mm/dd/yyyy and dd/mm/yyyyy

Threaded View

  1. #1
    Registered User
    Join Date
    04-28-2011
    Location
    Auckland
    MS-Off Ver
    Excel 2003
    Posts
    24

    Question date issue mm/dd/yyyy and dd/mm/yyyyy

    I have the below code that goes through pivot tables/charts and makes certain dates invisible:

    Sub ChangeStartingDatePoint_Macro_modifiedToIncludeSpecificDates()
    Application.ScreenUpdating = False  '' this makes last 7 days visible
                                        '' assuming all days are visible beforehand
                                       
    Dim ws As Worksheet
    Dim objPT As PivotTable
    Dim objPTField As PivotField
    Dim objPTItem As PivotItem
    Dim LastDate As Date
    Dim ALastDate As Date
    Dim BLastDate As Date
    Dim FirstDate As Date
    
    FirstDate = Sheets("Sheet1").Range("A2").Value
    LastDate = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp)
    MsgBox "Last Date is:" & LastDate & vbCrLf & "First Date is:" & FirstDate & vbCrLf
    ALastDate = Sheets("Sheet2").Range("G5").Value
    BLastDate = Sheets("Sheet2").Range("H5").Value
    MsgBox "ALastDate is:" & ALastDate & vbCrLf & "BLastDate is:" & BLastDate & vbCrLf
     
    For Each ws In Worksheets
    ws.Visible = xlSheetVisible
      For Each objPT In ws.PivotTables
     ' MsgBox "Pivot table name: " & objPT.Name & vbCrLf & "Sheet name: " & ws.Name & vbCrLf & "test"
        With objPT
            '
            ' Run through all the row fields
            For Each objPTField In .RowFields
                       MsgBox "objPTField is:" & objPTField.Value & vbCrLf
                For Each objPTItem In objPTField.VisibleItems
                    If objPTItem.Value < ALastDate Then
                    '' MsgBox "FirstDate is:" & FirstDate & vbCrLf & "Lastdate7 is:" & LastDate7 & vbCrLf
                        MsgBox "objPTItem is:" & objPTItem.Value & vbCrLf
                        objPTItem.Visible = False
                    
                    'FirstDate = FirstDate + 1
                    End If
                
                    If objPTItem.Value > BLastDate Then
                    '' MsgBox "FirstDate is:" & FirstDate & vbCrLf & "Lastdate7 is:" & LastDate7 & vbCrLf
                        MsgBox "objPTItem is:" & objPTItem.Value
                        objPTItem.Visible = False
                    
                    'LastDate = LastDate - 1
                    End If
                
                Next 'objPTItem
            Next 'objPTField
        End With
    
    FirstDate = Sheets("Sheet1").Range("A2").Value
      Next objPT
    Next ws
    Application.ScreenUpdating = True
    End Sub
    But this line of code shows me that the date is in the format mm/dd/yyyy. How do I rectify this?

     MsgBox "objPTItem is:" & objPTItem.Value & vbCrLf

    As this messes up my if statement


                    If objPTItem.Value < ALastDate Then
                    '' MsgBox "FirstDate is:" & FirstDate & vbCrLf & "Lastdate7 is:" & LastDate7 & vbCrLf
                        MsgBox "objPTItem is:" & objPTItem.Value & vbCrLf
                        objPTItem.Visible = False
                    
                    'FirstDate = FirstDate + 1
                    End If

    It is stored in the format dd/mm/yyyyy, but not sure how to correct the conversion??

    Any ideas?

    Thanks

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1