CODEWITHSUNDEEP
excelvba
Raffi95
Raffi95

Reputation: 47

Fill a cell and format to date -> date is displayed wrong

I have filled many cells with different dates. example:

After filling these cells with a vba macro, I want to change the format into the short month descriptions ("Jan", "Feb", "Mar", etc..)

So I changed the format with vba into "MMM", but the format is not effected on the cells. I have to go in the Cell with "F2" press enter, and then the cell will change his format to "Jan, Feb, etc.."

How can I make sure that the cell value is changed directly after changing the format?

Upvotes: 0

Views: 543

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149325

Here is a simple way to enter and format dates. See if this helps?

Logic:

  1. Enter start date in A1
  2. Enter the rest of the dates in the range in one go without looping. We use Edate for this purpose.
  3. Format the entire range in one go without looping.

Code:

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    
    Set ws = Sheet1 '<~~ Change this to relevant sheet
    
    With ws
        .Range("A1").Formula = "=DATE(2020,1,1)"
        .Range("A2:A12").Formula = "=EDATE(A1, 1)"
        '~~> Optional
        .Range("A1:A12").Value = .Range("A1:A12").Value
        '~~> Format entire range in one go
        .Range("A1:A12").NumberFormat = "MMM"
    End With
End Sub

Upvotes: 0

FunThomas
FunThomas

Reputation: 29582

Your cells probably don't contain a date but a string that looks like a date. Formatting a cell as Date that contains a string doesn't convert the content into a date automatically. As the content of the cell is not matching to the number format, the formatting is ignored.

If you enter the cell, it looks to Excel as if you are editing the content. When leaving the cell, Excel tries to understand what you typed. As the cell format is set to a date format, Excel assumes that the string 01.01.2020 is meant to be a date and converts it into a date. After that, the number format can be applied and the cell shows as "Jan".

So probably you have to change the macro that fills your cell by writing real dates.

Dim d As Date, row As Long

d = DateSerial(2020, 1, 1)
For row = 1 To 12
    With ActiveSheet.Cells(row, 1)
        .Value = d
        .NumberFormat = "MMM"
    End With
    d = DateAdd("m", 1, d)
Next

BTW: To check if a cell contains a string, a date or a number, type in the immediate window:

? vartype(activecell.Value)

5 = numeric, 7 = date, 8 = string

Upvotes: 0

Related Questions