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

0
votes

I have filled many cells with different dates. example:

  • 01.01.2020
  • 01.02.2020
  • 01.03.2020
  • 01.04.2020
  • 01.05.2020
  • etc.. (for each month one entry)

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?

2
excelvba
How did you change the format? Showing the VBA code you used will be helpful. Theoretically, if you have a range, let us say, rng, in order to do what you need ist is enough to use rng.NumberFormat = "MMM". – FaneDuru

2 Answers

0
votes

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

0
votes

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