+ Reply to Thread
Results 1 to 13 of 13

Baffled by mysterious mm/dd/yyyy date format

  1. #1
    Registered User
    Join Date
    11-12-2020
    Location
    Leeds, UK
    MS-Off Ver
    Mac O365
    Posts
    14

    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

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,409

    Re: Baffled by mysterious mm/dd/yyyy date format

    Hi Iwaddo,

    Dates in Excel are the number of days since 1/1/1900 (in PC Excel) and since 1/1/1904 (in Mac Excel). The display of the dates can be changed to many different formats but the number of days is the base for all these formats. If you have the cell as a text string instead of a number (of days since..) you need to change/fix it.

    Look at:
    https://smallbusiness.chron.com/date...-pc-68917.html
    https://answers.microsoft.com/en-us/...a-7c894b68e891

    Hope this helps.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,364

    Re: Baffled by mysterious mm/dd/yyyy date format

    This may help:

    https://www.ozgrid.com/Excel/free-tr...a1lesson14.htm

    The main thing to be aware of is that dates in VBA are US-centric, that is, they will default to MM/DD/YYYY rather than the European DD/MM/YY.

    Basically, it's a PitA.
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  4. #4
    Registered User
    Join Date
    11-12-2020
    Location
    Leeds, UK
    MS-Off Ver
    Mac O365
    Posts
    14

    Re: Baffled by mysterious mm/dd/yyyy date format

    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.

  5. #5
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    32,364

    Re: Baffled by mysterious mm/dd/yyyy date format

    Please post the code and, ideally, a sample workbook.

  6. #6
    Registered User
    Join Date
    11-12-2020
    Location
    Leeds, UK
    MS-Off Ver
    Mac O365
    Posts
    14

    Re: Baffled by mysterious mm/dd/yyyy date format

    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.

  7. #7
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,174

    Re: Baffled by mysterious mm/dd/yyyy date format

    Please come back here anyway - don't leave the thread hanging! if you find the issue, report what it was and mark the thread as solved. If not, then we can assist further.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  8. #8
    Registered User
    Join Date
    09-02-2020
    Location
    USA
    MS-Off Ver
    2016
    Posts
    48

    Re: Baffled by mysterious mm/dd/yyyy date format

    Possibly related to concatenating it with a string?

    strComparitor & dtCriteria2

    try
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    11-12-2020
    Location
    Leeds, UK
    MS-Off Ver
    Mac O365
    Posts
    14

    Re: Baffled by mysterious mm/dd/yyyy date format

    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
    Attached Files Attached Files

  10. #10
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,503

    Cool Hi ! Try this !


    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 !

  11. #11
    Forum Expert
    Join Date
    11-24-2013
    Location
    Paris, France
    MS-Off Ver
    Excel 2003 / 2010
    Posts
    5,503

    Lightbulb Cherry on the cake !


    According to your attachment to prove how Excel well works even under VBA :

    PHP Code: 
    Sub Demo4NoobCherryOnTheCake()
        
    MsgBox "Excel : " & [COUNTIF(DataTable[Date],">"&G1)]
    End Sub 
    ► Do you like it ? ► So thanks to click on bottom left star icon Add Reputation !
    Last edited by Marc L; 04-12-2021 at 09:35 PM.

  12. #12
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,421

    Re: Baffled by mysterious mm/dd/yyyy date format

    Pro tip: In VBA, if you convert a serial date into a Long number, then date format is irrelevant.

    Please Login or Register  to view this content.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  13. #13
    Registered User
    Join Date
    11-12-2020
    Location
    Leeds, UK
    MS-Off Ver
    Mac O365
    Posts
    14

    Re: Baffled by mysterious mm/dd/yyyy date format

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Update Date Field From User Form in dd/mm/yyyy format instead of mm/dd/yyyy format
    By LUCKY92 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-18-2020, 12:11 PM
  2. Replies: 3
    Last Post: 04-25-2019, 11:05 AM
  3. Convert Date format from Text format reading m/d/yyyy to dd/mm/yyyy
    By bridge4444 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-23-2017, 11:22 PM
  4. Replies: 8
    Last Post: 10-02-2013, 06:23 PM
  5. Replies: 7
    Last Post: 11-18-2012, 02:28 PM
  6. [SOLVED] Imported data contains strings dd.mm.yyyy how can I convert to date format dd/mm/yyyy inVB
    By Boormo in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-05-2012, 05:48 PM
  7. [SOLVED] opening excel file -> date format problem: DD/MM/YYYY vs MM/DD/YYYY
    By yung in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2005, 09:06 AM

Tags for this Thread

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