Setting Date Format from PC Settings

[joeKen]

Hi,

I'm trying to convert a cell's date format to match that of the user. I have no idea what the user of the spreadsheet will wish to see, so I'm hoping to pick up the date format from there pc settings instead of hard coding "DD/MM/YYYY" or forcing them to select a date format from a combo box.

Everywhere I check on web has it hard coded...!

If (c.Value2 <> "") Then
      dt = CDate(c.Value2)
      c.NumberFormat = "DD/MM/YYYY"
      c.Value2 = dt
  End If

Any help very welcome and appreciated.

Joe

 

[xlbo]

To be honest, unless this is for something that will be used across different countries, I would just use the country's native format eg dd/mm/yyyy for UK or mm/dd/yyyy for US

Rgds, Geoff

"Having been erased. the document thjat you are seeking. Must now be retyped"

Please read FAQ222-2244 before you ask a question

 

[joeKen]

Doesn't sound hopeful

This will be used in many countries so I would like to avoid going down the version by country route if I possibly can.

Cheers, Joe

 

[TonyJollans]

Hi Joe,

A couple of options - all the information you need should be available from the Excel [blue]Application.International[/blue] property - see Help for all the codes.

Alternatively, to format a date according to current locale settings, you could use the GetDateFormat API ..

[blue]Type SYSTEMTIME
    wYear As Integer
    wMonth As Integer
    wDayOfWeek As Integer
    wDay As Integer
    wHour As Integer
    wMinute As Integer
    wSecond As Integer
    wMilliseconds As Integer
End Type

Declare Function GetDateFormat _
        Lib "kernel32" _
        Alias "GetDateFormatA" _
            (ByVal Locale As Long, _
             ByVal dwFlags As Long, _
                   lpDate As SYSTEMTIME, _
             ByVal lpFormat As String, _
             ByVal lpDateStr As String, _
             ByVal cchDate As Long) _
        As Long

Sub Test()
    MsgBox GetDateInLocalFormat(Date) 
End Sub

Function GetDateInLocalFormat(myDate As Date) As String

    Dim strLocalDate As String
    Dim STDate As SYSTEMTIME
    
    STDate.wDay = Day(myDate)
    STDate.wMonth = Month(myDate)
    STDate.wYear = Year(myDate)
    
    strLocalDate = String(255, 0)
    GetDateFormat 0, 0, STDate, vbNullString, strLocalDate, Len(strLocalDate)
    strLocalDate = Left$(strLocalDate, InStr(strLocalDate, Chr$(0)) - 1)
    
    GetDateInLocalFormat = strLocalDate
    
End Function
[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[PHV]

Have you tried this ?
c.NumberFormat = "General date"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[joeKen]

Hi,

Thanks for everybodies help on this forum. I managed to create my app which wouldn't have been possible without your help. Here's what I ended up doing for the date conversions.

To convert the number field into a date field I created this routine:

Sub ConvertRangeToDateFormat(chkRange As Range, strDateFormat As String)
    
    Dim c As Range
    Dim dt As Date
    
    For Each c In chkRange
        If (c.Value2 <> "") Then
            dt = CDate(c.Value2)
            c.NumberFormat = strDateFormat
            c.Value2 = dt
        End If
    Next

End Sub

To get the date format I call this function at the start of the routine.

Function GetDateFormat() As String

    Select Case Application.International(xlDateOrder)
        Case 0 '= month-day-year
            GetDateFormat = _
                "MM" & Application.International(xlDateSeparator) & _
                "DD" & Application.International(xlDateSeparator) &_
                "YYYY"
        Case 1 '= day-month-year
            GetDateFormat = _
                "DD" & Application.International(xlDateSeparator) & _
                "MM" & Application.International(xlDateSeparator) & _
                "YYYY"
        Case 2 '= year-month-day
            GetDateFormat = _
                "YYYY" & Application.International(xlDateSeparator) & _
                "MM" & Application.International(xlDateSeparator) & _
                "DD"
        Case Else
             GetDateFormat = "DD/MM/YYYY"
    End Select

End Function

As an aside, this is a useful routine that I use a lot.

Sub CalculateFinalRowColum(ws As Worksheet, _
        ByRef FinalRow As Integer, ByRef FinalCol As Integer)
    
    FinalRow = ws.Cells(65535, 1).End(xlUp).Row
    FinalCol = ws.Cells(1, 255).End(xlToLeft).Column

End Sub

 

[xlbo]

Thanks for sharing your resultant code Joe

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question