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 day has 8 values, so I have 8 rows with day 1, eight rows with day 2, and so on.

I want to add a month column, but I had zero success. I had tried date, month formulas and change the format of the cell, nothing seems to work.

I appreciate if you can help me.

Thanks in advance

Formulas and Functions
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Apr 14, 2023

    Cuto05 

    If it's what I think it is, you could use:

    =LET(
    dates, SEQUENCE(365, , DATE(2023, 1, 1)),
    REDUCE("All Dates", dates, LAMBDA(a, v, VSTACK(a, EXPAND(v, 8, , v))))
)
  • 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.

  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Apr 14, 2023

    Cuto05 

    As far as I could understand...

     One way to add a month column to your data would be to use a formula that takes into account the repeating pattern of the day values.

     For example, you could use the CEILING function in combination with the ROW function to calculate the month number for each row.

     

    Here’s an example formula that you could use in the first row of your month column:

    =CEILING((ROW()-ROW($A$1))/8/31+1,1)

     

    This formula assumes that your day values start in cell A1 and that there are 8 rows for each day.

    You can adjust the formula as needed to match your data.

     

    Once you have entered the formula in the first row of your month column, you can then copy it down to the rest of the rows in that column.

     

    I hope this helps!

     

    NikolinoDE

    I know I don't know anything (Socrates)

Resources