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]

it sees 1 april as 4 january, 2 april as 4 february, etc, untill 12 april as 4 december (the american way of showing the data as mmm/dd) and then 13 april is again normal ...

??? solution 2 of How to Change Date Formatting for Grouped Pivot Table Fields - Excel Campus

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Columns("L:L").NumberFormat = "mm/dd/yy;@"
Debug.Print "ran macro to set date format in pivot table"
End Sub


But are you sure all your dates in the source are really dates and a string looking like a date.
Can you group your date to a month for example ?

 

[arthurbr]

Most likely some of your dates are "text looking like dates" ( left aligned in cells without manual alignment) rather than real dates ( right-aligned)).
Formatting is only the way XL shows numbers to the outside world and does not affect the underlying values
Perhaps write a piece of code to mimic the sequence : Select date range - click Data - Text to columns - Finish, or do it manually, which should turn the "text" into real dates
If not some other techniques will be needed
There might be better techniques in VBA but I'm not savvy in these matters.

 

[clefev01]

it sees 1 april as 4 january, 2 april as 4 february, etc, untill 12 april as 4 december (the american way of showing the data as mmm/dd) and then 13 april is again normal ...

??? solution 2 of How to Change Date Formatting for Grouped Pivot Table Fields - Excel Campus

Yes it is what I understand. However, in the table and in the pivot table it is displayed properly. Only in VBA code the date is not correct...

 

[Alex Blakenburg]

Try this:

MsgBox pi.Value & " - Day=" & Day(Format(pi.Value, "mm/dd/yyyy"))

 

[clefev01]

Try this:

MsgBox pi.Value & " - Day=" & Day(Format(pi.Value, "mm/dd/yyyy"))

Thank you. This helps to get the day correctly.

Now, the problem is how to compare pi.value with another date. I tried

            If Format(Pi.Value, "mm/dd/yyyy") < Format("22/03/2022", "mm/dd/yyyy") Then
                MsgBox Format(Pi.Value, "mm/dd/yyyy") & " inferior " & Format("22/03/2022", "mm/dd/yyyy")
            Else
                MsgBox Format(Pi.Value, "mm/dd/yyyy") & " superior " & Format("22/03/2022", "mm/dd/yyyy")
            End If

Unfortunately, it does not work. For some date it does not work properly. Here I compare April 1st and March 22nd

 

[BSALV]

use the numberformat YYYYMMDD to compare both dates,
but at first look, pi.value is what type ? date, long, integer ???
then make the 2nd date the same type and compare
something like (without testing)

i=sgn(cint(pi.value)-cint(dateserial(2022,3,22))) '=the sign of the substraction of both integer values)
select case i
 case -1:msgbox "smaller"
case 0:msgbox "equal"
case +1 :msgbox "greater"
end select

 

[Alex Blakenburg]

See if this works for you, for both the If statement comparison and getting the day.
(I prefer Debug.Print, change that to msgbox if you prefer that)

        Dim dt As Date
        Dim dtPvt As Date
       
        dt = DateSerial(2022, 3, 26)
        dtPvt = Application.Text(pi.Value, "d/mm/yyyy")
       
        If dtPvt < dt Then
            Debug.Print dtPvt & "  inferior; " & dt & " day " & Day(dtPvt)
        Else
            Debug.Print dtPvt & "  superior; " & dt & " day " & Day(dtPvt)
        End If

 

[clefev01]

See if this works for you, for both the If statement comparison and getting the day.
(I prefer Debug.Print, change that to msgbox if you prefer that)

        Dim dt As Date
        Dim dtPvt As Date
      
        dt = DateSerial(2022, 3, 26)
        dtPvt = Application.Text(pi.Value, "d/mm/yyyy")
      
        If dtPvt < dt Then
            Debug.Print dtPvt & "  inferior; " & dt & " day " & Day(dtPvt)
        Else
            Debug.Print dtPvt & "  superior; " & dt & " day " & Day(dtPvt)
        End If

That works perfectly. I still have a lot of things to learn in order to code properly :D

Thank you very much !

 

[clefev01]

use the numberformat YYYYMMDD to compare both dates,
but at first look, pi.value is what type ? date, long, integer ???
then make the 2nd date the same type and compare
something like (without testing)

i=sgn(cint(pi.value)-cint(dateserial(2022,3,22))) '=the sign of the substraction of both integer values)
select case i
 case -1:msgbox "smaller"
case 0:msgbox "equal"
case +1 :msgbox "greater"
end select

pi.value is a string (field of my pivot table) but refers to a date in my source table. As I had an issue some time to get the day and month I tried to format the date like a number with YYYYMMDD format but it is just a workaround.

I tried your code but does not work due to pi.value type. However I do not know why I get an overflow with cint(dateserial(2022,3,22))

Anyway I got an answer from @Alex Blakenburg so no worry

Thank you to all who worked on my problem. Really appreciate !