Baffled by mysterious mm/dd/yyyy date format

Hi, I have spent literally hours debugging one of my VBA routines. Everything was looping perfectly and all the results were as expected accept one. Eventually I worked out that it was the dates. In three of the tests I was using these dates, 01/01/2017, 01/01/2021 & 01/01/2012, for the fourth test I was using today(), currently the 11/04/2021. However, for some inexplicable reason I have yet to work out VBA is treating 11/04/2021 as 04/11/2021 so not giving me the expected results. As soon as I changed the macro input to be 04/11/2021 it all worked as expected. Of course, I never saw the problem on my three earlier tests because 01/01/2012 is exactly the same as 01/01/2012 :-)

I've checked all the settings on my iMac running 10.15.7 using Excel 16.49 I can find and it all seems to be set to United Kingdom or English. Excel is in UK English for all other date activities.

I'd be grateful if someone can help me understand why VBA is reading dd/mm/yyyy dates from a cell into an array as though they were in a mm/dd/yyyy format however when I use debug.print to show the date in intermediate widow it prints dd/mm/yyyy, exactly as it should. This is why it took so long to debug!!

I'm very confused, Thank you for your help.

Regards

Thank you , these are both helpful, I've had a quick look but not solved my problem, I may need to study further.

My date is stored in a cell as a date, in this case today() is 44,297. It if formatted as a short date so reads as 11/04/2021.

I read data including this date into a Variant.

If I debug.print from the variant using Debug.Print varSourceCriteria(i, 5) it gives 11/04/2021.
If I then assign it to a variable , dtCriteria2 = varSourceCriteria(i, 5)and debug.print the variable it gives 11/04/2021

yet when I use dtCriteria2 in this calculation WorksheetFunction.CountIfs(rgSort1, strCriteria1, rgSort2, strComparitor & dtCriteria2) it counts as though dtCriteria2 is 04/11/2021


I've clearly more studying to do.

Thank you for your help.

I will need to create a much smaller version of my model that demonstrates the problem, of course doing this may help me also solve the problem, I will come back here if I still need help.

Thank you very much for your support.

Possibly related to concatenating it with a string?

strComparitor & dtCriteria2

try

Please Login or Register  to view this content.

Hi, so I have managed to produce a cutdown version of my macro which uses a test set of data.

The macro is a little clunky. I have left in loads of debug.print statements I was using for testing.

The results of the macro are written to the main sheet to prove the problem.

I have provided loads of notes and guidance to highlight the issue.

The problem is that the macro is reading a date that is in dd/mm/yyyy format but when I use the date in a worksheet function it behaves as though it is mm/dd/yyyy.

Thank you for your help

First, as a reminder VBA is still in US format whatever the worksheet layout.

So your bad as you transform the date to a text not respecting the US format via the VBA function Format
rather than the US - or even UK - format or just keeping it as numeric !

According to your attachment some VBA demonstrations as a very beginner starters :

PHP Code: 

Sub Demo4Noob1()
    MsgBox "Numeric : " & Application.CountIf([DataTable[Date]], ">" & [G1].Value2)
End Sub

Sub Demo4Noob2()
    MsgBox "Text US format : " & Application.CountIf([DataTable[Date]], ">" & Format([G1].Value2, "yyyy/m/d"))
End Sub

Sub Demo4Noob3()
    MsgBox "Text UK format : " & Application.CountIf([DataTable[Date]], ">" & Format([G1].Value2, "m/d/yyyy"))
End Sub 


► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

Thank you very much for the response and examples provided based on my attached example.

I will take the time to work through each to see which suits my overall solution the best but I tried converted the date to a Long number and it worked first time.

Once again, thank you so much, all the support has been really great and much appreciated.