If the user input is always mmddyy, regardless of their international
settings, you can use

Dim S As String
Dim MM As String
Dim DD As String
Dim YY As String
Dim DT As Date
Dim DateSep As String
Dim LocalDateString As String

S = "080509" ' text entry date
MM = Left(S, 2) ' month as string
DD = Mid(S, 3, 2) ' day as string
YY = Right(S, 2) ' year as string
DT = DateSerial(CInt(YY), CInt(MM), CInt(DD)) ' an actual Date
Debug.Print Format(DT, "short date")


Here, S is the text entered by the user. The variable DT is an actual
date converted from the parsed elements of S. The value of DT is the
same regardless of the users international settings, so you can use
that in any date calculation. The parameters to DateSerial are
always year, month, day, regardless of regional settings, so you can
always use DateSerial to get the date.

You can use the Format function with the named format "short date" to
return the value of DT in the user's local format, e.g., "mm/dd/yyy"
or "dd/mm/yy" or whatever the Windows setting is. If you need the date
only for calculations and not for display, just use the DT value. It
is independent of any international variation.


If, however, the input string by the user varies according to local
convention (e.g., one of mmddyy or ddmmyy or yymmdd), you can use code
like

Dim S As String
Dim MM As String
Dim DD As String
Dim YY As String
Dim DT As Date
Dim DateSep As String
Dim LocalDateString As String

S = "080509" ' text entry date
Select Case Application.International(xlDateOrder)
Case 0 ' mm dd yy
MM = Left(S, 2)
DD = Mid(S, 3, 2)
YY = "20" & Right(S, 2)
Case 1 ' dd mm yy
MM = Mid(S, 3, 2)
DD = Left(S, 2)
YY = "20" & Right(S, 2)
Case 2 ' yy mm dd
MM = Mid(S, 3, 2)
DD = Right(S, 2)
YY = "20" & Left(S, 2)
End Select

DT = DateSerial(CInt(YY), CInt(MM), CInt(DD))

Here, S is the text input. Application.International(xlDateOrder)
returns a value indicating the local date order. S is parsed according
to the xlDateOrder value. It is then converted with DateSerial to DT,
an actual Date value, invariant of regional settings. You can then use
DT in any date calculation.

To display DT in the local format, use

Debug.Print Format(DT, "short date")

The built in "short date" format code formats in the local convention.



Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)








On Fri, 7 Aug 2009 12:55:01 -0700, JT
wrote:

Sure.....Users enters the deposit date as mmddyy (080509). The macro
converts this to a string "8/5/09" and then converts it to a date (8/5/2009).
For the US cost centers this works great. Then the macor retrieves the
system date. I then use DateDiff to calculate the number of days between
each date.

The issue is with one cost center in Quebec where some of the machines have
French Canadian Regional settings on their PC.

The "8/5/09" string is converted into 2008-09-05. When this is compared to
the system date (2009-08-05) it is out of the 3 day range and they get an
error; when they shouldn't have.

I'm trying to get the 2 dates in sync so I can make an accurate comparison

Thanks for the help