DateValue Function

---

Named Arguments

No

Syntax

DateValue(stringexpression)

stringexpression

Use: Required

Data Type: String

Any of the date formats recognized by IsDate.

Return Value

Variant (Date).

Description

Returns a date variant (variant type 7) containing the date represented by stringexpression. The date value is formatted according to the short date setting defined by the Regional Settings applet in the Control Panel. DateValue can successfully recognize a stringexpression in any of the date formats recognized by IsDate. DateValue doesn't return time values in a date/time string; they are simply dropped. However, if stringexpression includes a valid date value but an invalid time value, a runtime error results.

Rules at a Glance

• The order of the day, the month, and the year within stringexpression must be the same as the sequence defined by the computer's regional settings.

• Only those date separators recognized by IsDate can be used.

• If you don't specify a year in your date expression, DateValue uses the current year from the computer's system date.

Example

Dim sDateExpression As String

sDateExpression = 10 & "/" & "March" & "/" & 1998

If IsDate(sDateExpression) Then
    Debug.Print DateValue(sDateExpression)
Else
    Debug.Print "invalid date"
End If

Programming Tips and Gotchas

• When working with dates, always check that a date is valid using the IsDate function prior to passing it as a function parameter.

• If stringexpression includes time information as well as date information, the time information is ignored; however, if only time information is passed to DateValue, an error is generated.

• DateValue handles two-digit years in the following manner: year arguments between and 29 are taken to be in the 21st Century (2000 to 2029), while year arguments between 30 and 99 are taken to be in the 20th Century (1930 to 1999). Of course, the safest way to specify a year is to use the full four digits.

• The current formats being used for dates are easier to discover on Windows NT than on Windows 95. On Windows NT, the date formats are held as string values in the following registry keys:

  
  

  Date Separator

  HKEY_CURRENT_USERControl PanelInternational, sDate value entry

  
  

  Long Date

  HKEY_CURRENT_USERControl PanelInternational, sLongDate value entry

  
  

  Short Date

  HKEY_CURRENT_USERControl PanelInternational, sShortDate value entry

• The more common approach to date conversion is to use the CDate function. Microsoft also recommends using the C... conversion functions due to their enhanced capabilities and their locale awareness.

Sidebar 4. Returning the Current Date Formats in Windows NT The example shows how to use the Windows API to return the current long and short date formats on Windows NT 4.0 machines. Unfortunately, Windows 95 exposes only the Locale ID in the registry unless individual elements have been changed individually, and there are a thousand and one API constants and function calls required to retrieve the settings. Such deep API work is really outside the scope of this book.

Example

Option Explicit

'declare the API Functions and constants required
Private Const HKEY_CURRENT_USER = &H80000001
Private Const KEY_ACCESS = &H3F
Declare Function RegOpenKeyEx Lib "advapi32.dll" Alias _
   "RegOpenKeyExA" (ByVal hKey As Long, _
   ByVal lpSubKey As String, ByVal ulOptions As Long, _
   ByVal samDesired As Long, phkResult As Long) _
   As Long
Declare Function RegQueryValueExNULL _
   Lib "advapi32.dll" Alias "RegQueryValueExA" _
   (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal lpReserved As Long, lpType As Long, _
   ByVal lpData As Long, lpcbData As Long) As Long
Declare Function RegQueryValueExString _
   Lib "advapi32.dll" Alias "RegQueryValueExA" _
   (ByVal hKey As Long, ByVal lpValueName As String, _
   ByVal lpReserved As Long, lpType As Long, _
   ByVal lpData As String, lpcbData As Long) As Long
Declare Function RegCloseKey Lib "advapi32.dll" _
   (ByVal hKey As Long) As Long

Public Function CurrentDateFormat(sType As String) _
   As String

   CurrentDateFormat = _
      QueryDateFormat("Control PanelInternational", _
                      "s" & sType & "Date")
End Function

Private Function QueryDateFormat(sKeyName As String, _
                 sValueName As String) As String
       
   On Error GoTo QueryDateFormat_Err
       
   Dim lReturn As Long            'API Call return value
  
   Dim lhKey As Long              'handle of opened key
   Dim sValueSetting As String    'date format setting
   Dim lCCh             As Long
   Dim lType            As Long
   Dim lValue           As Long
    
   'open the registry key
   lReturn = RegOpenKeyEx(HKEY_CURRENT_USER, sKeyName, _
                           0, KEY_ACCESS, lhKey)
   'get the length of the setting
   lReturn = RegQueryValueExNULL(lhKey, sValueName, _
                                 0&, lType, 0&, lCCh)
    
   If lReturn <> 0 Then
      Err.Raise 40000, App.Title, _
                "Can't get registry key value"
   Else
      'pad a string to the legnth of the setting
      sValueSetting = String(lCCh, 0)
      'query the setting
      lReturn = RegQueryValueExString(lhKey, sValueName, _
                0&, lType, sValueSetting, lCCh)
      If lReturn = 0 Then
         QueryDateFormat = Left$(sValueSetting, lCCh)
      Else
         QueryDateFormat = ""
      End If
   End If
   'close the registry key
   RegCloseKey (lhKey)
    
   Exit Function
    
QueryDateFormat_Err:
   MsgBox Err.Description
End Function
...which can be accessed simply from the client like this...
Option Explicit

Private Sub Command1_Click()
    MsgBox CurrentDateFormat("Long")
End Sub
Private Sub Command2_Click()
    MsgBox CurrentDateFormat("Short")
End Sub

See Also

CDate Function, DateSerial Function, IsDate Function