The format function in vba is changing the date. e.g for format("3/12/2009","DD/MM/YYYY"), the function returns 12/03/2009 where "3/12/2009" is what excel vba reads from a cell that has the value 12-Mar-2009 and the format as "dd-mmm-yyyy"
2 Answers
No it's not.
If a date-as-string is passed to the Format function, it will parse it using current regional settings. Your settings are obviously MM/DD/YYYY which is default for USA. Nothing prevents Excel from displaying a date as DD/MM/YYYY if set manually, but by default it would display MM/DD/YYYY.
To do: Stop reading dates as strings. Read them as dates.
dim d as date
d = activecell.value
1 Comment
barrowc
Or if you must use a String then put it in YYYY-MM-DD format: 2009-03-12 for your example. Still better to use a Date though
Had few times problem myself where VBA in Access reades most dates as europian but some as USA:
This DOES NOT work properly:
myRs.FindFirst ("Date =#" & myDate & "#")
This works:
myRs.FindFirst ("Date =#" & Format(myDate, "Long Date") & "#")
The long date (eg 01 January 2012) clearly makes the difference between month and day
