System Dates

[JT]

Is it possible to convert a system date in dd/mm/yy format to the a date
variable in the mm/dd/yy format?

 

[Jacob Skaria]

In VBA you can use Format
Msgbox Format(Date,"mm/dd/yy")

Can you elaborate your requirement when you say "to the a date variable"

If this post helps click Yes

 

[Rick Rothstein]

If by "date variable" you really meant a variable Dim'med "As Date", then the answer to your question is no... sort of. Dates do not have format until they are displayed... to VB, they are just floating point numbers with the integer part representing the number of days past "date zero" (December 31, 1899) and the decimal part representing the fractional part of a 24-hour day (6:00am would be 6/24 which equals 0.25). When you display a date, VB uses your computer's Regional Settings to figure out how to show the date to you. You can over ride this pre-determined output using the Format function. You can also assign the output from the Format function to a String variable, and the text assigned to the variable will be in the form you specified in the Format function. So, the answer to your question kind of depends on what it is you want your code to actually do.

 

[JT]

Thanks..........What I want to do is get the date in whatever format the
user's regional settings are set to in a variable. Then I want to take that
result and convert it to a US date, so I can compare it to the date the user
has entered (in a US format).

 

[Rick Rothstein]

I'm not sure exactly what problem you are encountering that prompted you to ask your question BUT, if your dates are stored as real Dates, the translations from system to system should be automatic. Remember, real dates are stored as floating point numbers which have no "format"... so the underlying value would be the same in the UK as it is in the US. Now, if your dates are stored as Text value... then there could be problems with conversions. Can you tell us a little more about your setup?

--
Rick (MVP - Excel)

Thanks..........What I want to do is get the date in whatever format the
user's regional settings are set to in a variable. Then I want to take that
result and convert it to a US date, so I can compare it to the date the user
has entered (in a US format).

 

[JT]

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

 

[Rick Rothstein]

There will *always* be problems when you vector through a String in order to get to a date value. How do your Quebec centers enter the deposit date (use the August 5, 2009 date as an example)... that is, do they enter it *exactly* the same as the other cost centers (same digit order) or do they to it differently (and if so, how)?

--
Rick (MVP - Excel)

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

 

[Mishell]

Always ask your macro to convert it to yyyy/mm/dd - 2009-08-05 - ( not
mm/dd/yyyy or any other format) in order to always get the same date
whatever the regional settings are.

Mishell

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

 

[Chip Pearson]

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)