Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
Is it possible to convert a system date in dd/mm/yy format to the a date
variable in the mm/dd/yy format? -- JT |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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 --------------- Jacob Skaria "JT" wrote: Is it possible to convert a system date in dd/mm/yy format to the a date variable in the mm/dd/yy format? -- JT |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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.
-- Rick (MVP - Excel) "JT" wrote in message ... Is it possible to convert a system date in dd/mm/yy format to the a date variable in the mm/dd/yy format? -- JT |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "JT" wrote in message ... Is it possible to convert a system date in dd/mm/yy format to the a date variable in the mm/dd/yy format? -- JT |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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) "JT" wrote in message ... 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "JT" wrote in message ... Is it possible to convert a system date in dd/mm/yy format to the a date variable in the mm/dd/yy format? -- JT |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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 -- JT "Rick Rothstein" wrote: 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) "JT" wrote in message ... 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "JT" wrote in message ... Is it possible to convert a system date in dd/mm/yy format to the a date variable in the mm/dd/yy format? -- JT |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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) "JT" wrote in message ... 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 -- JT "Rick Rothstein" wrote: 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) "JT" wrote in message ... 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "JT" wrote in message ... Is it possible to convert a system date in dd/mm/yy format to the a date variable in the mm/dd/yy format? -- JT |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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 "JT" wrote in message ... 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 -- JT "Rick Rothstein" wrote: 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) "JT" wrote in message ... 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "JT" wrote in message ... Is it possible to convert a system date in dd/mm/yy format to the a date variable in the mm/dd/yy format? -- JT |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
System Dates
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
dates from inhouse system download as text.Convert back to date? | Excel Worksheet Functions | |||
Using File System Object to retrieve paths filenames and dates | Excel Programming | |||
Subtract Dates based on 24 hour system | Excel Worksheet Functions | |||
error in getting system dates | Excel Programming | |||
Hide/Unhide Columns representing dates as per Current System Date! | Excel Programming |