How to calculate/set up number of days or years between two dates in Custom and Form records

            Dates are handled, numerically, as a number of seconds past midnight on January 1, 1970.


            This the number of seconds between two dates:  when4-when3
            This is the number of days:  (when4-when3)/86400+1


            Explanation: 86400 is the number of seconds in a day, so "/86400" converts seconds to days. The "+1" is because one would normally count both the first and last day, so that a membership from January 1 to January 10 would be for 10 days; since 10-1 is 9, so you need to add 1 to it.


            You can convert this to years by dividing by the number of days in a year:  ((when4-when3)/86400+1)/365.25


            The field that holds the formula for number of days or years should be an Amount field so that it can accommodate decimal points.
            Alternatively, if you'd prefer to round the result to the nearest year, you can do this by either:
            a. adding 0.5 to the formula: ((when4-when3)/86400+1)/365.25+0.5
            b. surrounding the whole formula with a round function: round(((when4-when3)/86400+1)/365.25)
            Updated: 27 Dec 2018 06:07 AM
            Helpful?  
            Help us to make this article better
            0 0