# 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.

4 Replies

# Re: Obtain a month column from only a day value

It would be nice if you share an excel sample file

# Re: Obtain a month column from only a day value

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.

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)

# Re: Obtain a month column from only a day value

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.

# Re: Obtain a month column from only a day value

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))))
)``````