VBA determine computer region

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Viperv10 - 25 May 2007 10:45 GMT

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.

Reply to this Message

Bob Phillips - 25 May 2007 12:17 GMT

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

Signature

HTH

Bob

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

>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
[quoted text clipped - 8 lines]
>
> Thanks.

Reply to this Message

Gary''s Student - 25 May 2007 18:18 GMT

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

Signature

Gary''s Student - gsnu200724

> 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
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

Dave Peterson - 25 May 2007 23:54 GMT

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

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

> 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
[quoted text clipped - 6 lines]
>
> Thanks.

Signature

Dave Peterson

Reply to this Message

Viperv10 - 27 May 2007 01:14 GMT

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.

> 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
[quoted text clipped - 6 lines]
>
> Thanks.

Reply to this Message

Dave Peterson - 27 May 2007 01:39 GMT

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.

> Thank you everyone for your posts however, you misread the original posting.
>
[quoted text clipped - 30 lines]
> >
> > Thanks.

Signature

Dave Peterson

Reply to this Message