Sign Up ›

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Dianna Raquel Perez GüerequeDianna Raquel Perez Güereque 

Exact difference on months

Hello:

I have 2 custom fields where i have date values. 

I need to calculate the exact diference in months between the final date and the start date. 
I have this formula:

MONTH(datevalue(FinalDate)) +12 - 
MONTH(StartDate)+12 * 
( 
YEAR(datevalue(FinalDate)) - 
YEAR(StartDate) 
-1)

it works, but for example, if i have as a final date: 08/11/2015 (today), and start date 07/30/2015 the formula gives me 1 month difference, but is not a month yet. 
How could i add the factor "day" to this formula to have the exact difference IN MONTHS, i want the result of this difference (08/11 & 07/15) be zero. (other words: i don't want a day difference as a result) 


Anyone has an option to do this?
(sorry for my baaaaaaaaaaaad english, regards!)
Best Answer chosen by Dianna Raquel Perez Güereque
William TranWilliam Tran
Just wrap your code by checking the day also. Try this: 
IF(DAY(datevalue(FinalDate)) >=DAY(StartDate) ,
MONTH(datevalue(FinalDate)) +12 - MONTH(StartDate)+12 * 
( YEAR(datevalue(FinalDate)) - YEAR(StartDate) -1),
MONTH(datevalue(FinalDate)) +12 - MONTH(StartDate)+12 * 
( YEAR(datevalue(FinalDate)) - YEAR(StartDate) -1) -1)
Thx

All Answers

William TranWilliam Tran
Just wrap your code by checking the day also. Try this: 
IF(DAY(datevalue(FinalDate)) >=DAY(StartDate) ,
MONTH(datevalue(FinalDate)) +12 - MONTH(StartDate)+12 * 
( YEAR(datevalue(FinalDate)) - YEAR(StartDate) -1),
MONTH(datevalue(FinalDate)) +12 - MONTH(StartDate)+12 * 
( YEAR(datevalue(FinalDate)) - YEAR(StartDate) -1) -1)
Thx
This was selected as the best answer
William TranWilliam Tran
Dianna,

Welcome to the Developer's Forum, Since you are new, here are some guidelines: 

As a common practice, if your question is answered, please choose 1 best answer. 
But you can give every answer a thumb up if that answer is helpful to you. 

This will help keep the forum clean and help future users determine what answers are useful and what answer was the best in resolving the user's issue. 

Thanks
Dianna Raquel Perez GüerequeDianna Raquel Perez Güereque
Oooooh, sure...
I wanted to make it so complicated with equivalences of days to months and was having a headache because some months have till 27 days to 31... 
And it was so easy, thank you so much William for your answer, i was really blocked with this....

Regards!
William TranWilliam Tran
The phrase:

If first you don't succeed, try try again!

should be changed to:

If first you don't succeed, then just ask someone :-)
Dianna Raquel Perez GüerequeDianna Raquel Perez Güereque
It should.... i tried with 3 different formulas :-P 
It seems that i like to make simple stuff, complicated hahaha.

Thanks again :-)