Show Month from Week #

Hello,

I'm creating a helper column that needs to show what month a week number was from.

For Example, I have a sheet with a [Date] column and [Week #] column.


I have second sheet that has a Week # and is pulling data from my first sheet based on a calculation using Week #.

In the second sheet, I'd like to create a helper column that displays the Month that the Week # is from, I.E. - Week 1 = January, Week 6 = Feb, etc...

Can this be done with a formula?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The easiest way to do this is in stages on a reference sheet.


    Step 1:

    Create a date type column and enter ever date from Jan 1 to Dec 31.


    Step 2:

    Create a text/number column called "Weeknumber" and use this formula:

    =WEEKNUMBER([Date Column]@row)


    Step 3:

    Create a text/number column called "Month Number" and enter the numbers 1 - 12 in it.


    Step 4:

    Create a text/number called "Month Text" and enter the desired text for each month.


    Step 5:

    Create a text/number column to house the final desired output of the month in relation tot he week number and use this formula

    =INDEX([Month Text]:[Month Text], MATCH(MONTH([Date Column]@row), [Month Number]:[Month Number], 0))


    Now you have a table that has every date, the corresponding week number, and the appropriate month text.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!