Need to track how many months are this year/vs next year or historical.

Hi all,

My team tracks projects that see savings over a 12 month period.

So I have a Start Date column -- that is the date that the savings will start, but I need to be able to determine how many months will count towards this year, vs next year (or even last year).

So for example, if I have a project with a start date of 6/1/2023 --- that project will have 6 months of savings this year, and 6 months of saving that will roll over into next year.

I currently have 2 columns --- CY Months and Carry Over Months --- with the formula below, if I have a start date of 1/1/24 -- its giving me 12 for the number of month for this year. Please help!

=IFERROR(IF(OR([CURRENT YEAR]# > YEAR([End Date]@row)), "0", IF(YEAR([End Date]@row) <> [CURRENT YEAR]#, 12 - MONTH([Start Date]@row) + 1, MONTH([End Date]@row) - 0 - 1)), "")

Answers

  • Zachary Hall
    Zachary Hall ✭✭✭✭✭✭

    Hello,

    Here is the solution I would propose to see how many months the project has in the current year:

    • For CY Months: =IFERROR(IF(YEAR([Start Date]@row)=YEAR(TODAY()),12-MONTH([CURRENT YEAR]@row),""),"")

    I would note though that "Current Year" will change in less than a month, so if what you are looking for is instead a snapshot of how many months in the "Originating Year" you could use this simpler formula instead:

    • =IFERROR(12-MONTH([Start Date]@row),"")

    Hope this helps!

    Best,

    Zach Hall

    Training Delivery Manager / Charter Communications

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!