Start date of the month for the previous 13 months rolling

I wanted to have a rolling 13 month table that and hoped to have a formula for the first date of the month for 13 months. I have a helper column with today's date in it and have listed the months and the start date as per the attached. Is there a formula that I can put in the start date column to have this roll on from one month to the next so it's only showing 13 months from the current month back?


Tags:

Best Answer

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓

    Hi @B Young

    You can use two formulas to achieve the result you need

    Formula 1 next to current month - =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

    Having the current date adjust is the key, you then add the second formula against month 1 and drag it upwards against the rest of the months.

    To the first cell

    The formula reads the cell below basically taking 1 away from the month and accounting for the Dec - Jan transition by taking 1 away from the year and making the month 12 in that case.

    =IF(MONTH([Start Date]13) = 1, DATE(YEAR([Start Date]13) - 1, 12, 1), DATE(YEAR([Start Date]13), MONTH([Start Date]13) - 1, 1))

    *I've adjusted the formula to reflect your column name 'Start Date'

    Hope that helps

    Thanks Paul

Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Answer ✓

    Hi @B Young

    You can use two formulas to achieve the result you need

    Formula 1 next to current month - =DATE(YEAR(TODAY()), MONTH(TODAY()), 1)

    Having the current date adjust is the key, you then add the second formula against month 1 and drag it upwards against the rest of the months.

    To the first cell

    The formula reads the cell below basically taking 1 away from the month and accounting for the Dec - Jan transition by taking 1 away from the year and making the month 12 in that case.

    =IF(MONTH([Start Date]13) = 1, DATE(YEAR([Start Date]13) - 1, 12, 1), DATE(YEAR([Start Date]13), MONTH([Start Date]13) - 1, 1))

    *I've adjusted the formula to reflect your column name 'Start Date'

    Hope that helps

    Thanks Paul

  • B Young
    B Young ✭✭

    Thank you - this answers the question. Appreciate your help.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!