excel date format not same as in vba

Question

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?

possible duplicate of Excel VBA date formats. Have a look at this answer which explains a little bit of what you're dealing with. — Jean-François Corbett
– Jean-François Corbett, Commented Dec 6, 2013 at 8:55
Thanks for the ans, but I think that is a little bit different than my situation, it seems excel will save number format to m/d/yyyy (default format for US locale) when I use date format begin with *, so I can't distinguish the actual format or that is system format. However it may give me a way to do something with this problem. — Prisoner
– Prisoner, Commented Dec 7, 2013 at 4:15
This question of mine and the answer may help: stackoverflow.com/q/9839676/973283 — Tony Dallimore
– Tony Dallimore, Commented Dec 8, 2013 at 14:10

Sam · Accepted Answer · 2013-12-06 09:05:13Z

1

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.

answered Dec 6, 2013 at 9:05

Sam

7,3333 gold badges27 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Prisoner Over a year ago

Thanks, but my result is same, coz rng.NumberFormat return m/d/yyyy, it's no different between Application.Text and Format for this case.

Jean-François Corbett Over a year ago

How is Format not complete?

Sam Over a year ago

@Jean-FrançoisCorbett This question I asked for instance.

Jean-François Corbett Over a year ago

Ha! Right, apparently I forgot that I had answered it :-)

Sam Over a year ago

@Jean-FrançoisCorbett Yes I did spot that :)

|

WGS · Accepted Answer · 2013-12-06 05:45:32Z

0

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.

answered Dec 6, 2013 at 5:45

WGS

14.2k5 gold badges50 silver badges51 bronze badges

3 Comments

WGS Over a year ago

@Alex: Based on Region and Language in Control Panel, it's Short date: M/d/yyyy and Long date: ddd, MMMM, dd, yyyy. Even if I change it to MM/dd/yyyy, the results are the same. :)

Prisoner Over a year ago

Thanks and I have tried, but the result are same as I did before. I have set whole column with the same date format, so I believe this not related to Application.Caller. BTW, may you help to check the cell format for A1? Date with Default format should begin with *, which will affected by regional setting

WGS Over a year ago

Weird. Changed things around and I'm getting the same error you're getting. 12/06/2013 is now turning into 12/6/2013 when I used the default Date setting. Methinks VBA reads the date differently from how Excel reads it in the regional setting.

Prisoner · Accepted Answer · 2013-12-07 04:37:59Z

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

Collectives™ on Stack Overflow

excel date format not same as in vba

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related