Date formatting in excel VBA-CodePudding

I have made a macro (by button press) in VBA that is supposed to give me an oversight of a monthly predicted budget ranging from the earliest month of all my tasks to the latest month of all my tasks, where each tasks budget is spread evenly over its duration.

My first problem in the following code, is at when i try to populate headers (as months) into sheet2 (ws2), it is very inconsistent in the date formatting.

I want the months i sheet2 to be in the following format: Jan-21, but it only seems to get some of them right. Mostly it gets the format and even the year wrong.

The code is as follows:

Sub Budget()
    Dim numTasks As Integer, numMonths As Integer
    Dim i As Integer, j As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim firstStartDate As Date, lastEndDate As Date
    Dim taskValue As Double, taskDuration As Integer

    ' Set the worksheets
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2") ' Sheet2 has to be created beforehand - the code doest create new sheet

    ' Clear the contents of Sheet2
    ws2.Cells.Clear

    ' Get the number of tasks
    numTasks = ws1.Range("A5", ws1.Range("A5").End(xlDown)).Rows.Count
    

    ' Get the first start date and last end date
    firstStartDate = ws1.Range("C5").Value
    lastEndDate = ws1.Range("D5").Value
    For i = 6 To numTasks
        If ws1.Range("C" & i).Value < firstStartDate Then
            firstStartDate = ws1.Range("C" & i)
        End If
        If ws1.Range("D" & i).Value > lastEndDate Then
            lastEndDate = ws1.Range("D" & i)
        End If
    Next i
    

    ' Calculate the number of months between the first start date and last end date
    numMonths = DateDiff("m", firstStartDate, lastEndDate)   1

    ' Create the table header
    ws2.Cells(1, 1) = "Task"
    For i = 1 To numMonths
        ws2.Cells(1, i   1) = Format(DateAdd("m", i - 1, firstStartDate), "mmm-yy")
        
    Next i

    ' Populate the table with data for each month of each task
    For i = 2 To numTasks   3
        ws2.Cells(i, 1) = ws1.Cells(i   3, 1)
        taskValue = ws1.Cells(i, 2).Value
        taskDuration = DateDiff("m", ws1.Cells(i, 3).Value, ws1.Cells(i, 4).Value)   1
        For j = 1 To numMonths
            If ws1.Cells(i, 3) <= DateAdd("m", j - 1, firstStartDate) And ws1.Cells(i, 4) >= DateAdd("m", j - 2, firstStartDate) Then
                ws2.Cells(i - 3, j   1) = taskValue / taskDuration
                ws2.Cells(i - 3, j   1).NumberFormat = "#,##0.0"
            End If
        Next j
    Next i
    ' Sum up the monthy budget
    ws2.Cells(numTasks   2, 1) = "Total"
    For i = 2 To numMonths   1
        ws2.Cells(numTasks   2, i) = "=SUM(" & ws2.Cells(2, i).Address(False, False) & ":" & ws2.Cells(numTasks   1, i).Address(False, False) & ")"
        ws2.Cells(numTasks   2, i).NumberFormat = "#,##0.0"
    Next i
        
End Sub

I have tried different versions of the Format command etc with different "mm-yy", "mmmm-yyyy" and other setup configurations in the following:

  For i = 1 To numMonths
        ws2.Cells(1, i   1) = Format(DateAdd("m", i - 1, firstStartDate), "mmm-yy")
        
    Next i

CodePudding user response：

When you use Format in VBA, you convert the date into as string.

Assuming a start date of 1st September 2022: In the first iteration of your loop, you will get "Sep-22" (depending on your regional settings, this might be different as the name of the month is printed in your local language). You can check this:

For i = 1 To numMonths
    Dim s As String
    s = Format(DateAdd("m", i - 1, firstStartDate), "mmm-yy")
    Debug.Print s
    ws2.Cells(1, i   1) = s
Next i

Now the next step is that you write this string (it's no longer a date!) into Excel. Excel will take this input and tries to make sense out of it. And Excel (not VBA) will not only look to the string you pass - but will also consider the formatting of the cell to guess what the content is.

If the cell you are writing your String to is formatted as Text, you will see exactly the string that was created with the format command (and your problem is solved). However, if it is formatted as General or Date or Number, the outcome is completely different. Excel will guess that the string you entered is somehow a string representing a date and try to convert it. However, it will assume that the string you are sending contains three letters of a month - and a day, while the year is missing. And, as the string comes from VBA, the month name is in English. As the year is missing, Excel assumes the current year, so Apr-21 will be converted into 21st of April 2022. However, if the month name isn't a valid English month name, the conversion fails, and Excel will write the original string into the cell.

I did some experiments (german language) to show what I mean:

As you can see, for Jan and Feb, Excel converted the string into a date while December and March couldn't be converted (because this is "Dez" and "Mär" in German but not in English)

So you have 2 possibilities:
a) (as already mentioned): Format the cell as Text before you write something into it:

For i = 1 To numMonths
    ws2.Cells(1, i   1).NumberFormat = "@"
    ws2.Cells(1, i   1) = Format(DateAdd("m", i - 1, firstStartDate), "mmm-yy")
Next i

However, this has the drawback that the cell contains - a string.

b) From my point of view, the better alternative is to write a date value into the cell and format the cell accordingly:

 For i = 1 To numMonths
    ws2.Cells(1, i   1).NumberFormat = "mmm-dd"
    ws2.Cells(1, i   1) = DateAdd("m", i - 1, firstStartDate)
Next i