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.

Graiae

which format string are you using? the key values don't change with culture. – Palatalized

that doesn't exist in .net - perhaps you could write something on your own. – Palatalized

@DanielA.White how would you do it, with simple string manipulation and how would that method look like, what properties would you look at, I don't really know all cultures where it could be different and a string manipulation method that's valid for every culture and every user could have changed his settings to something totally random. – Graiae

could this answer help: https://mcmap.net/q/694190/-excel-date-formatting – Palatalized

superuser.com/a/917519/165333 has some workarounds that use VBA. – Stem

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

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

Deonnadeonne

Very nice. I was unaware of the International property and even less aware of how it might be useful. – Cattycornered