I have two columns one for start date and one for end date. I am try to an automatic calculation of the months between the to dates. However if the start date is 15th or after it counts as the next month (example 01/15/16 would count as February).
I was thinking an if formula if the start date is days 1:14 than
IF(D6<14,(year(e6)-year(d6))*12+month(e6)-month(d6),(year(e6)-year(d6))*12+month(e6)-month(d6)+1). but I cannot get it to work.
Any other suggestions?
You can use the DATEDIF function inside an `IF statement to do this.
In F6 use the IF statement to check if the "start date is 15th or after", which the result will modify the DATEDIF calculation.
=IF(DAY(D6)>=15, DATEDIF(D6,E6, "m")-1, DATEDIF(D6,E6, "m"))
This checks if the Start Date to see if the day is greater than 15. If it is it uses the DATEDIF function to calculate the months, minus 1 month.
If the day is not greater than 15, it uses the standard DATEDIF calculation.
Note: my images use differnet cells.
There's a very simple way:
=(E6-D6)/30.4375
The result will tell you how many months have passed between the 2 dates. The number with which it's divided is the average number of days in a year over 4 years (to include 1 leap year).
This works because of the way Excel stores dates. It's actually stored as a number of days passed since 1/1/1900 so arithmetic operations work.
Note that if you set number type to not show decimals it would round 16 days to full month. If you want to avoid this, just round it down, and then the result will be number of full months passed:
=INT((E6-D6)/30.4375)
