DateTime get the letters representing day, month, year from the currentculture

Question

I would like to write a method that gives me the 3 letters - representing the day, month, year - from a datetimeformat.

So in en-US culture my desired return values would be (plus the separator)

• d
• M
• y
• /

in de-DE the same method should return

• T (Tag = day)
• M (Monat = month)
• J (Jahr = year)
• . (Separator)

I had a look into the DateTimeFormatInfo class form CurrentCulture but could only find the separator do you have any idea how I can get the rest?

var culture = Thread.CurrentThread.CurrentCulture;
Console.WriteLine(culture.DateTimeFormat.DateSeparator);

I need this because the TEXT function inside Excel doesn't accept the format from the InvariantCulture and only handles it from the currentculture.

So something like this isn't valid in german Excel version:

=TEXT(NOW();"dd.MM.yyyy")

this needs to be set with

=TEXT(NOW();"TT.MM.JJJJ")

(it doesn't matter if its done with VBA, it doesn't get translated) You can have a look at the following http://www.rondebruin.nl/win/s9/win013.htm

The string format can be anything since the user can input it as he likes and I need to translate it to the current culture.

Answer 1

You can do this in VBA: DateTimeStrings will return an array of the date codes and separator.

Option Explicit
Function DateTimeStrings() As Variant
    Dim s(3)
    With Application
        s(0) = .International(xlDayCode)
        s(1) = .International(xlMonthCode)
        s(2) = .International(xlYearCode)
        s(3) = .International(xlDateSeparator)
    End With

DateTimeStrings = s

Debug.Print s(0), s(1), s(2), s(3)

End Function

EDIT: I'm not sure how you want to use this, but, for example, to insert a formula similar to what you have above, (eg: =TEXT(A1,"dd/mm/yyy") in B1 and in the appropriate language, you could select A1 and run the following macro:

=============================================

Sub AddTextFunction()
    Dim R As Range, C As Range
    Dim sFormula As String
    Dim V As Variant
     Dim D As String, M As String, Y As String, sep As String

Set R = Selection

For Each C In R
    V = DateTimeStrings()
        D = V(0)
        M = V(1)
        Y = V(2)
        sep = V(3)

    sFormula = "=text(RC[-1],""" & D & D & sep & M & M & sep & Y & Y & Y & """)"
    C.Offset(0, 1).FormulaR1C1 = sFormula
Next C

End Sub

===================================================