CODEWITHSUNDEEP
excelvba
clattenburg cake
clattenburg cake

Reputation: 1222

Wrong VBA date format in Excel cell

I'm trying to print out different date formats in my Exel sheet using VBA. However, I can't seem to output the "yyyy-mm-dd" format, or the "mmm dd, yyyy" formats. The cells don't seem to print out the correct formats. For instance: 

'Declare new date variables
Dim LongDateFmt As String
Dim ShortDateFmt As String
Dim MediumDateFmt As String
Dim NewDateFmt As String
'Change the date to the new submission date
    MediumDateFmt = Format(Date, "yyyy-mm-dd")
    Range("A1").Value = MediumDateFmt
    LongDateFmt = Format(Date, "Long Date")
    Range("A2").Value = LongDateFmt
    ShortDateFmt = Format(Date, "Short Date")
    Range("A3").Value = ShortDateFmt
    NewDateFmt = Format(Date, "MMMM dd, yyyy")
    Range("A4").Value = NewDateFmt

A1 prints out 13/06/2013, A2 prints 13-Jun-13, A3 prints out 13/06/2013 and A4 prints 13-Jun-13 as well.

Is this a settings problem or is my code wrong?

Upvotes: 0

Views: 14258

Answers (2)

paxdiablo
paxdiablo

Reputation: 881453

Format doesn't set the format of a cell to what you want, it simply gives you a string of a specified format. Inserting that string into a cell won't necessarily give you the results you want since the current formatting of the cell may have an impact.

If you want to change formatting of the cell itself, you need to set its NumberFormat property, with something like:

Range("A1").NumberFormat = "yyyy-mm-dd"

Upvotes: 2

JosieP
JosieP

Reputation: 3410

you ought to change the cell format and not use formatted strings

with Range("A1")
   .Value = Date
   .Numberformat = "yyyy-mm-dd"
end with

for instance

Upvotes: 4

Related Questions