DateTime gets letters representing the day, month, year from the current culture.

Question

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

So, in en-US culture, my desired return values ​​will be (plus a separator)

• d
• M
• at
• /

in de-DE the same method should return

• T ( T ag = day)
• M ( M onat = month)
• J ( J ahr = year)
• . (Separator)

I looked at the DateTimeFormatInfo class of CurrentCulture , but could only find the delimiter. Do you know 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 does not accept the format from the InvariantCulture and only processes it from the current culture.

So something like this is not valid in the German version of Excel:

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

it needs to be installed using

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

(it doesn’t matter if this is done with VBA, it does not translate) You can see the following http://www.rondebruin.nl/win/s9/win013.htm

The format of the string can be any, as the user can enter it at his discretion, and I need to translate it into the current culture.

Answer 1

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

 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 (for example: =TEXT(A1,"dd/mm/yyy") in B1 and in the corresponding language, you can 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 

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