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.

Why no input it as YYYYMMDD, or DDMMMYYYY, no ambiguity then.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Viperv10" wrote in message
...
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.

Why pull your hair out??

Sub date_in()
Dim d As Date
Dim s As String
s = InputBox("Enter date: ", "18 January 2007")
d = DateValue(s)
MsgBox (d)
End Sub

Using a universal format means never have to guess if its Dec first or Jan
twelveth
--
Gary''s Student - gsnu200724


"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.

Or use a calendar control so the user can point and click.

Ron de Bruin has a link to a calendar control he
http://www.rondebruin.nl/calendar.htm

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

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.

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