Excel date format is not the same as in vba

Question

I found a problem for the date format in excel, the display format of which is not the same as numberformatin vba and I created a test for this case.

Regional Setting for Short Date: MM/dd/yyyy

modules:

Function RetrieveNumberFormat() As String
    Dim rng As Range
    Set rng = Application.Caller    
    RetrieveNumberFormat = rng.NumberFormat       
    Set rng = Nothing
End Function

Function SetDate() As String
    Dim rng As Range    
    Set rng = Application.Caller    
    SetDate = Format(Now, rng.NumberFormat)    
    Set rng = Nothing
End Function

In excel

Col A (Date w default format) | Col B (MM/dd/yyyy)      | Col C (dd-mmm-yyyy)     | Col D (dd/mm/yyyy)      | Col E ([$-404]mm/dd/yyyy)
=Now()                        | =Now()                  | =Now()                  | =Now()                  | =Now()
=RetrieveNumberFormat()       | =RetrieveNumberFormat() | =RetrieveNumberFormat() | =RetrieveNumberFormat() | =RetrieveNumberFormat()
=SetDate()                    | =SetDate()              | =SetDate()              | =SetDate()              | =SetDate()

Date with standard format (Col A):

Result:

Can I find out why Excel changed System Date Format MM/dd/yyyyto m/d/yyyy, and is there a workaround?

Answer 1

Try using it instead Application.Text.

So your SetDate function will look like

Function SetDate() As String
    Dim rng As Range    
    Set rng = Application.Caller    
    SetDate = Application.Text(Now, rng.NumberFormat)    
    Set rng = Nothing
End Function

My experience with the function Formatis that it is not complete.

Answer 2

. , .

:

:

:

Function RetrieveNumberFormat(rng As Range) As String
    Application.Volatile
    RetrieveNumberFormat = rng.NumberFormat
End Function

Function SetDate(rng As Range) As String
    Application.Volatile
    SetDate = Format(Now, rng.NumberFormat)
End Function

, .

Answer 3

, , , .

, , , excel date formats that begin with (*) m/d/yyyy, US locale (, Excel ) .

, excel, excel , , VBA , , autofilter.

I have a function SetDate:

Function SetDate(refCell As Range) As String
    If refCell.NumberFormat = "m/d/yyyy" And refCell.Text = Format(refCell.Value, "Short Date") Then
        SetDate = Format(Now, "Short Date")
    Else
        SetDate = Format(Now, refCell.NumberFormat)
    End If
End Function

However, this method still has a problem when working with the date format: dd-MMM-yyyywhich will display as dd-MM-yyyy.