VBA Date values when inserted to Excel cells chang

Question

I have date variables formatted like: 25_December_2010

Once i use

Dim strDate As String
strDate = "25_December_2010"
strDate = Replace(strDate,"_"," ")
MsgBox strDate

Surely enough a MsgBox pops up and gives me: 25 December 2010.

However once i try to put the value into a cell for example:

Sheets("Sheet1").Range("A1").Value = strdate

Instead of populating the cell with: 25 December 2010; Excel acts on it's own accord and populates the cell with the vexing entry configuration: 25-Dec-2010!

How can I have my cell populated with no hyphen characters inbetween and not having the month name trimmed?

Answer 1

This code puts the date into A1 in the format you write that you want:

Option Explicit
Sub WriteDate()
    Dim strDate As String
strDate = "25_December_2010"
strDate = Replace(strDate, "_", " ")
MsgBox strDate

With Sheets("Sheet1").Range("A1")
    .Value = strDate
    .NumberFormat = "dd mmmm yyyy"
End With

End Sub

I'm not sure if it is necessary, but for clarity, since strDate is a string data type, I would probably use

.Value = CDate(strDate)

Explicitly converting it to the Date data type, before writing it to the worksheet, might be of value in non-English versions, but I've not checked that specifically.

Answer 2

The Excel sheet is not wrong, so stop saying it is. A date is a count of the number of days since a start date. So a date is a NUMBER. You can format it how you want.

This is VBA, excel is similar though the starting dates are different.

Date variables are stored as IEEE 64-bit (8-byte) floating-point numbers that represent dates ranging from 1 January 100 to 31 December 9999 and times from 0:00:00 to 23:59:59. Any recognizable literal date values can be assigned to Date variables. Date literals must be enclosed within number signs (#), for example, #January 1, 1993# or #1 Jan 93#.

Date variables display dates according to the short date format recognized by your computer. Times display according to the time format (either 12-hour or 24-hour) recognized by your computer.

When other numeric types are converted to Date, values to the left of the decimal represent date information while values to the right of the decimal represent time. Midnight is 0 and midday is 0.5. Negative whole numbers represent dates before 30 December 1899.

Date 8 bytes January 1, 100 to December 31, 9999

This is what recording it in Excel shows.

    Selection.NumberFormat = "d mm yyyy"

This works here for me

Sub DateF()

Dim strDate As String
        strDate = "25_December_2010"
        strDate = Replace(strDate, "_", " ")
        MsgBox strDate
        Worksheets("Sheet1").Range("A1").NumberFormat = "dd mm yy"

        Worksheets("Sheet1").Range("A1").Value = strDate
End Sub

I also changed it from sheet. to worksheet.

Answer 3

Use a custom date format:

Sheets("Sheet1").Range("A1").NumberFormat = "dd mmmm yy" //A1 = 25 December 10