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](http://stackoverflow.com/questions/19801598/excel-vba-date-formats). Have a look at [this answer](http://stackoverflow.com/a/19809413/119775) which explains a little bit of what you're dealing with. — Jean-François Corbett, Dec 06 '13 at 08: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, Dec 07 '13 at 04:15
This question of mine and the answer may help: http://stackoverflow.com/q/9839676/973283 — Tony Dallimore, Dec 08 '13 at 14:10

score 1 · Answer 1 · answered Dec 06 '13 at 09:05

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 06 '13 at 09:05

Sam

7,245
3
25
37

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. – Prisoner Dec 07 '13 at 04:17
How is `Format` not complete? – Jean-François Corbett Dec 07 '13 at 15:37
@Jean-FrançoisCorbett [This question](http://stackoverflow.com/questions/11143746/how-do-i-format-time-in-hmm-excel-format-using-format-function-in-vba) I asked for instance. – Sam Dec 07 '13 at 15:47
Ha! Right, apparently I forgot that I had [answered it](http://stackoverflow.com/a/11146092/119775) :-) – Jean-François Corbett Dec 07 '13 at 19:52
@Jean-FrançoisCorbett Yes I did spot that :) – Sam Dec 07 '13 at 19:53
Thank you!!! This was driving me crazy! The format function would format the date as it appeared in the function bar, but it wouldn't display properly in the cells. – Macromika Dec 31 '14 at 15:47

score 0 · Answer 2 · answered Dec 06 '13 at 05:45

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 06 '13 at 05:45

WGS

13,969
4
48
51

@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. :) – WGS Dec 06 '13 at 06:05
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` – Prisoner Dec 06 '13 at 06:19
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. – WGS Dec 06 '13 at 06:26

score 0 · Answer 3 · answered Dec 07 '13 at 04:37

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

excel date format not same as in vba

3 Answers3

Linked