VBA - Pivot table - day and month are sometime reversed

[clefev01]

Hello

It is my first post as usually I can find answer in existing post. But since two days I get crazy with my problem.

With VBA, I want to select some specific dates in a pivot table field. Hereunder my example

All the dates are stored in a table with a date format dd-mmm-yy (eg: 14-Mar-22)

To explain my problem I wrote this piece of code :

Set pt = Sheets("tcd non reçus").PivotTables("PT_non_recu")
    ' change field as needed
    Set pf = pt.PivotFields("expected date")

    For Each Pi In pf.PivotItems
        If Pi.Value <> "NULL" And Pi.Value <> "#N/A" And Not IsError(Pi.Value) Then
            MsgBox Pi.Value & " - Day=" & Day(Pi)
        End If
    Next Pi

The problem ? For the first dates until march I get the right day - Day(Pi) - (23, 25, 29, ...) but starting the value '01/04/2022' the day is wrong as it says 4 instead of 1, 5, 8..

=> OK

=> KO

If someone has an idea, he/she is welcome !

Thank you very much for your help

 

[BSALV]

integer is until +32,000, my error, a long-variable is better, so use cLng instead of cInt.
But as the other solution works, it's not necessary anymore.

 

[clefev01]

integer is until +32,000, my error, a long-variable is better, so use cLng instead of cInt.
But as the other solution works, it's not necessary anymore.

Thanks for the explanation. Have all a Good day.

 

[Alex Blakenburg]

@BSALV, I also played around with CLng but couldn't get it to work, I get an error 13 Type Mismatch.
I can stop the error happening by doing a CDate first but then I again get the error of 1 Apr to 12 Apr reversing month and day.
It seems that some VBA functions use US format while some of the conversion functions use your region settings.

I opted to use Application.Text to avoid the early VBA conversion to US format. Using the equivalent VBA function "Format" seems to be convert the data to US format if it can before applying the format function and goes off the rails when the day is between 1 to 12.

 

[Alex Blakenburg]

@clefev01 - Thank your trying all our suggestions and letting us know what worked and what didn't. Glad we were able to help.

 

[BSALV]

i agree with the observations of @Alex Blakenburg in #13.

Sub Test()

     Set pt = Sheets("tcd non recus").PivotTables("PT_non_recu")
     Set pf = pt.PivotFields("expected date")

     For Each Pi In pf.PivotItems
          If Pi.Value <> "NULL" And Pi.Value <> "#N/A" And Not IsError(Pi.Value) Then
               mydate = Convert_US_Date(Pi.Value)
               MsgBox Pi.Value & vbLf & Format(mydate, "long date")
          End If
     Next Pi

End Sub

Function Convert_US_Date(US_Date As String) As Date
     sp = Split(Replace(Replace(US_Date, ".", "/"), "-", "/"), "/")
     Convert_US_Date = DateSerial(sp(2), sp(0), sp(1))
End Function

 

[Alex Blakenburg]

@BSALV - just confirming that your post #15 worked on my test data.

 

[BSALV]

great !
And i just checked Calendar date - Wikipedia to confirm that MDY is mostly US-format.