Apex Code Development You need to sign in to do that
Don't have an account?
Teeps54 Trying to create a date formula to calculate number of relative months between 2 dates
Hi all,
I'm not sure this can be done with a formula but would like to confirm. I know I can do this in Apex but if possible it would be nice to have it update via a formula instead of having to schedule something.
I'm trying to create a formula which will tell me the relative difference in months between 2 dates to one decimal place.
e.g. if dates are:
Start Date = 28/01/22 and
End Date = 25/07/22
There would be 5 full months and the remainder:
days between 28/06/22 and 25/07/22 is 27 days
divided by
days between 28/06/22 and 28/07/22 is 30 days
is 0.9
which added together would give a final value of 5.9 months.
For the purposes of this:
- if the Start or End Date day is over 28, I would just make it 28 and
- in calculating the difference I'm not including the last day.
Thanks in advance!
I'm not sure this can be done with a formula but would like to confirm. I know I can do this in Apex but if possible it would be nice to have it update via a formula instead of having to schedule something.
I'm trying to create a formula which will tell me the relative difference in months between 2 dates to one decimal place.
e.g. if dates are:
Start Date = 28/01/22 and
End Date = 25/07/22
There would be 5 full months and the remainder:
days between 28/06/22 and 25/07/22 is 27 days
divided by
days between 28/06/22 and 28/07/22 is 30 days
is 0.9
which added together would give a final value of 5.9 months.
For the purposes of this:
- if the Start or End Date day is over 28, I would just make it 28 and
- in calculating the difference I'm not including the last day.
Thanks in advance!
Dividing by 30 is going to give you slightly inaccurate results because not every month is 30 days.
If you are only concerned about difference in the actual month - ie July 30th to Aug 1st = 1 month
Try this:
(MONTH(Custom_End_Date__c) + (12*(YEAR(Custom_End_Date__c)-YEAR(TODAY()))) - MONTH(TODAY())
Kindly mark it as the best answer if it works for you.
Thanks & Regards,
Priya Ranjan