The Problem (Q-score 4, ranked #90th of 95 in the VBA Core archive)
The scenario as originally posted in 2013
I need to format these dates into yyyy/mm/dd to query a MySQL database – looking at these two statements, you’d think they would produce the same result, right?
dateOne = Format(#9/1/2013#, "yyyy/mm/dd")
dateTwo = Format(#9/1/2013#, "yyyy/mm/dd")
dateOne is now equal to 2013/09/01 , while dateTwo is equal to 9/1/2013. Am I crazy? This happens at the very begininng of my script, I’ve got Option Explicit on and I know it’s saving to the right variables..
Sub main()
Dim dateOne, dateTwo As Date
Dim answer As Integer
answer = MsgBox("Would you like to generate a Transfer Report?", vbYesNoCancel + vbQuestion)
If answer <> 6 Then Exit Sub
'dateOne = InputBox("Input the first (earliest) date in mm/dd/yyyy format")
'dateTwo = InputBox("Input the second (latest) date in mm/dd/yyyy format")
dateOne = Format(#9/1/2013#, "yyyy/mm/dd")
dateTwo = Format(#9/1/2013#, "yyyy/mm/dd")
Now dateOne is 2013/09/01, and dateTwo is 9/1/2013.
I’d originally had them at different dates, to query a range- before banging my head into my keyboard, to make absolutely sure I was performing the function the same way on both, I copied the text in dateOne, pasted it a line down, and changed the variable to dateTwo. Exact same command, executed two lines apart, producing different results. The script is almost done, but how, why, is it doing what it’s doing?
Maybe I could ask the user to enter it in yyyy/mm/dd format, but I really want to know why this is happening. Can anyone help?
Why the Win32 API declaration is fragile here
This problem involves a Declare statement, which means 32-bit vs 64-bit compatibility is in play. Office 64-bit requires the PtrSafe keyword and LongPtr data types for any handles — the most common root cause of the exact symptom described.
The Verified Solution — niche answer (below median) (+7)
10-line VBA Core pattern (copy-ready)
There are two things wrong with your routine that are combining to produce these seemingly illogical results:
Sub main()
Dim dateOne, dateTwo As Date
Dim answer As Integer
.
.
.
dateOne = Format(#9/1/2013#, "yyyy/mm/dd")
dateTwo = Format(#9/1/2013#, "yyyy/mm/dd")
First, Format(..) creates a Variant String, but you appear to be treating it as though it makes a Date.
Secondly this line: Dim dateOne, dateTwo As Date does not declare two Date variables as you likely believed when you wrote it. Rather, it declares dateTwo to be a Date variable but it declares dateOne to be a Variant (the default) because you didn’t make it anything else. To make them both Dates you would need Dim dateOne As Date, dateTwo As Date.
So taking these two facts together, what happens to dateOne is that Format makes the string as you intend it to, and then since the target variable is a Variant, stores it there as a Variant String.
dateTwo works differently, however, as it first makes the date-string as you tell it to (“2013/09/01”) but then sees that you are telling it to assign a String to a Date which are different data types, so it then converts the string back into a date, but uses your local system’s NLS settings to determine how to interpret the string which (in your case) causes the month and day numbers to be swapped.
Clear?
When to Use It — classic (2013–2016)
Ranked #90th in its category — specialized fit
This pattern sits in the 94% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.
What changed between 2013 and 2026
The answer is 13 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.