excel date format not same as in vba

user_1050927 0
Viewed 3747

I found an issue for date format in excel, which the display format is not as same as the numberformat in vba and I have create 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 default format (Col A):

Date format

Result:

Result

May I know why Excel changed System Date Format MM/dd/yyyy to m/d/yyyy, and is there a way to workaround?

vba excel date
3 Answers

Try using Application.Text instead.

So your SetDate function would 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 Format function is that it is not that complete.

user_13215160

Might be a problem with how your function is set up. Modified them and here are my results.

Formula Set-up:

Formula

Result:

Result

Modified functions:

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

Please see if this works on your end.

user_25487210
Related