If the values in the cells are really dates with a custom format, don't use the
date as string. Just pick up the value as a date.

Dim myDate as date
mydate = worksheets("sheet999").value
msgbox mydate + 1

If the values are really strings--or just numbers, then I don't think you're
going to have any luck determining what the date is. Those values won't change
no matter what order my date format is.

But maybe I missed the point again.

Viperv10 wrote:

Thank you everyone for your posts however, you misread the original posting.

The dates are already in the spreadsheet based on very complex formulas in
the excel spread sheet THAT CANNOT BE CHANGED. Format Canadian DD/MM/YYYY

Therefore the date is not being INPUT it is being picked up from the
spreadsheet. I then do a Dateadd("D",1) to add one day to the date.

Example:
Dim d as date
d = String in US format (11/11/2007)

US Region
Dateadd("d",1) = 11/12/2007 (wrong this would be a month change not a day
change)

Canada Region
Dataadd("d",1) = 12/11/2007 (correct day changes not month)

There has to be way to determine region, this has to be automated and can't
be left to the user to determine date functionality.

Thanks.

"Viperv10" wrote:

I have a macro for an Excel spreadsheet that I used to add dates.
Unfortunately I wrote it on a computer that was in the Canada region
DD/MM/YYYY and when I moved it to a computer in US region MM/DD/YYYY it broke
as it was adding on days.

How can I use VBA to determine wether the date format is in MM/DD/YYY or
DD/MM/YYYY the reason I need this I am inputing the day as a string (it has
to be this way) in the format of DD/MM/YYYY and I need to add 1 day to it.

Thanks.

--

Dave Peterson