VBA Dates: Cannot Get the Format Correct

Question

I'm trying to convert a date from something like Month/Day/Year to fully spelled out, like May 31st, 2014.

I hit a roadblock. Currently, I'm using this, and when the Message Box pops up, if has the correct date (May 31st, 2014), but once I write it to a cell, it converts to a number (From 5/31/14 to 41790). I'm lost and would love some assistance. Thanks!

Dim whatever as String
whatever = Format("5/31/14","mmmm dd, yyyy")
MsgBox whatever
ActiveWorkbook.Activesheet.Cells(1,1) = whatever

I have a program that uses the data from the sheet to run a Mail Merge in word, so I'm trying to get the entire date written out and not just simply format the cell, because Word takes the raw data (from what I know.)

Look at the MONTHNAME function and just write the value directly to the cell... something like whatever = Monthname(myDate) & " " & Day(myDate) & ", " & Year(myDate) — Dave
– Dave, Commented Jul 14, 2016 at 14:39
add ActiveWorkbook.ActiveSheet.Cells(1,1).NumberFormat = "@" before placing the data in . — Scott Holtzman
– Scott Holtzman, Commented Jul 14, 2016 at 14:39
I used your method Scott. It worked! Thank you so much :D All of you really — Zack Mcgee
– Zack Mcgee, Commented Jul 14, 2016 at 14:50

cyboashu · Accepted Answer · 2016-07-14 14:39:00Z

2

When you are trying to display your format in the cell the correct way is to change the cell's number format and then set the cell's value

e.g.

Cells(1,1).NumberFormat="MMM DD, YYYY"
Cells(1,1).Value2= Date

answered Jul 14, 2016 at 14:39

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

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

2 Comments

SierraOscar Over a year ago

You can set the format before or after in Excel, it makes no difference as you're formatting the display value anyway - not the cell's value. That aside - this is the correct answer for what OP wants to do.

Dave Over a year ago

Does it though? OP's example desired date format was May 31st, 2014 - IIRC, this would give May 31, 2014? Willing to acknowledge that I might be wrong though!

Gary's Student · Accepted Answer · 2016-07-14 15:53:38Z

1

To get a date in ordinal format, consider:

Public Function OrdinalDate(d As Date) As String
    Dim dy As Long, mnth As String, yr As Long
    Dim s As String
    ar = Array("th", "st", "nd", "rd", "th", "th", "th", "th", "th", "th")

    dy = Day(d)
    mnth = MonthName(Month(d))
    yr = Year(d)

    If dy > 10 And dy < 20 Then
        s = "th"
    Else
        s = ar(dy Mod 10)
    End If

    OrdinalDate = dy & s & " " & mnth & " " & yr
End Function

Of course to remove the ordinal designation does not require VBA. With the ordinal form in B1, use:

=DATEVALUE(SUBSTITUTE(B1,MID(B1,FIND(" ",B1)-2,2),""))

and we are back where we started.

edited Jul 14, 2016 at 15:53

answered Jul 14, 2016 at 15:10

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Collectives™ on Stack Overflow

VBA Dates: Cannot Get the Format Correct

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related