calculate number of full months between start and end dates
I'm looking for a formula that will calculate the number of full months between 2 dates that will be used for calculating a goal amount by multiplying a monthly amount x the number of full months (partial months are prorated). In my example, I'm using
=ROUNDDOWN((([End Date]@row + 1) - [Start Date]@row) / 30.425, 0)
It seems if I change the 30.425 to another value such as 30 or 30.25 or 30.4325, it causes some of my other calculations to be incorrect but 30.425 doesn't seem to work for those highlighted.
Any help would be greatly appreciated!
Answers
-
I actually had to do something similar today and used the below style formula to find the months. You may need to add one or subtract one but It seems to be what I needed hopefully it helps you as well. Make sure that ( in front of the year otherwise it may not work.
=(YEAR([End Date]@row) - YEAR([Start Date]@row)) * 12 + MONTH([End Date]@row) - MONTH([Start Date]@row)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.1K Get Help
- 444 Global Discussions
- 140 Industry Talk
- 472 Announcements
- 5K Ideas & Feature Requests
- 83 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 497 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!