Forum Discussion

Cuto05's avatar
Cuto05
Copper Contributor
Apr 13, 2023

Obtain a month column from only a day value

Hi, I have this issue. I'm working with a column that only has day numeric value. From 1-31 (January) and then it's start over from 1-28(February) until the end of the year. To add complexity every d...
Formulas and Functions
Rodrigo_'s avatar
Rodrigo_
Steel Contributor
Apr 14, 2023

Cuto05 

My suggestion is to make a custom formula in VBA to determine the month value based on the day value. You can then use this custom formula in your worksheet to populate the month column. Here's an example of a custom formula that you can use:

 

Function GetMonth(dayValue As Integer) As String
    Dim monthValue As Integer
    
    If dayValue <= 31 Then
        monthValue = 1 ' January
    ElseIf dayValue <= 59 Then
        monthValue = 2 ' February
    ElseIf dayValue <= 90 Then
        monthValue = 3 ' March
    ElseIf dayValue <= 120 Then
        monthValue = 4 ' April
    ElseIf dayValue <= 151 Then
        monthValue = 5 ' May
    ElseIf dayValue <= 181 Then
        monthValue = 6 ' June
    ElseIf dayValue <= 212 Then
        monthValue = 7 ' July
    ElseIf dayValue <= 243 Then
        monthValue = 8 ' August
    ElseIf dayValue <= 273 Then
        monthValue = 9 ' September
    ElseIf dayValue <= 304 Then
        monthValue = 10 ' October
    ElseIf dayValue <= 334 Then
        monthValue = 11 ' November
    Else
        monthValue = 12 ' December
    End If
    
    GetMonth = MonthName(monthValue)
End Function

 

 

Then use =GetMonth(A1) (assuming your day values are in column A) in your worksheet to get the corresponding month value.

Resources