function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Dave The RaveDave The Rave 

Calculate calendar years between 2 dates

I have two fields RegFrom__c and RegUntil__c.

I would like to calculate how many calendar years there are between 2 dates WHERE:

CertRegFrom__c= 01-06-2018
CertRegUntil__c = 02-06-2023

This is a period of 5 years, but 6 calendar years, so result of the formula should be 6, the formula below returns 5. 

between the dates above you have the following calendar years 2018, 2019, 2020, 2021, 2022, 2023.

If CertRegFrom = 01-01-2018 AND CertRegUntil = 31-01-2022 then the formula must equal = 5
  
(CertRegUntil__c - CertRegFrom__c) /365

Can anyone help me adapt this formula?

Dave
Best Answer chosen by Dave The Rave
Chandra@AtlantaChandra@Atlanta
Dave,

use the below formula

(ABS(YEAR(CertRegUntil__c)-YEAR(CertRegFrom__c))) + (DAY(CertRegUntil__c)/DAY(CertRegUntil__c))

I hope this is helpful

"Appreciate your feedback"

All Answers

Rohit B ☁Rohit B ☁
Please try to use below code:-
CertRegFrom__c.daysBetween(CertRegUntil__c)/365;

Thanks,
Rohit B
Dave The RaveDave The Rave
Hi Rohit, unfortunately this did not work. Maybe I should have mentioned that that I created a formula field. So field X = the result of the formula.

See image below, error is  Error: Unknown function CertRegFrom__c.daysBetween. Check spelling.

User-added image
Dave The RaveDave The Rave
The field is NumberOfCalendarYears__c, maybe there is another way to populate this field with the result of the formula, other than what I am doing. 
Chandra@AtlantaChandra@Atlanta
Dave,

What is the issue here? does the above formula did not calculate the value correctly?

"Appreciate your feedback"
Dave The RaveDave The Rave
The formula does calculate the number of years between 2 dates. But I need the formula to calculate the number of calendar years. For example 01/06/18 to 31/12/18 is 6 months but in one calendar year 2018, so here formula should = 1. and see my other notes .
Chandra@AtlantaChandra@Atlanta
Dave,

use the below formula

(ABS(YEAR(CertRegUntil__c)-YEAR(CertRegFrom__c))) + (DAY(CertRegUntil__c)/DAY(CertRegUntil__c))

I hope this is helpful

"Appreciate your feedback"
This was selected as the best answer
Dave The RaveDave The Rave
Thanks for your help AltlantaFalcon, the formula works like a dream...
Marketplace 4Marketplace 4

Chandra@Atlanta
This accepted answer doesn't seem right: (ABS(YEAR(CertRegUntil__c)-YEAR(CertRegFrom__c))) + (DAY(CertRegUntil__c)/DAY(CertRegUntil__c))

The (DAY(CertRegUntil__c)/DAY(CertRegUntil__c)) expression at the end is just 1. Wouldn't it be easier to just add 1 like this?


(ABS(YEAR(CertRegUntil__c)-YEAR(CertRegFrom__c))) + 1