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
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!