CODEWITHSUNDEEP
vbadateexcel
Prisoner
Prisoner

Reputation: 1857

excel date format not same as in vba

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?

Upvotes: 4

Views: 3861

Answers (3)

Prisoner
Prisoner

Reputation: 1857

I'm not sure is it good enough to solve my question yet, but feel free to comment or enhance this.

The problem I encounter seems that excel will save date formats that begin with (*) to m/d/yyyy, the default format for US locale (Well, Excel is created by US company), instead of actual date format.

It work fine when we input value directly in excel, excel will change the date format automatically, however, when I use VBA for output, this may produce a problem, especially when using autofilter.

I have enhance SetDate function:

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 have an issue when work with date format: dd-MMM-yyyy, which excel will display as dd-MM-yyyy.

dd-MMM-yyyy

Upvotes: 0

Sam
Sam

Reputation: 7313

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.

Upvotes: 1

WGS
WGS

Reputation: 14179

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.

Upvotes: 0

Related Questions