How to caculate days in a year factoring leapyears

[#1]

Good morning, i've a minor issue im not sure how to resolve, I am trying to calculate age of debt for our finance team.

Ive used the script below to calculate age but not factoring in leapyears. This can lead to possible errors where a balance could span across 2 financial years.

if(today()-Due_Date>=0 and Today()-Due_Date<=365,'0-1 Years',

     if(today()-Due_Date>=366 and Today()-Due_Date<=730,'1-2 Years',

     if(today()-Due_Date>=731 and Today()-Due_Date<=1096,'2-3 Years',

     if(today()-Due_Date>=1097 and Today()-Due_Date<=1461,'3-4 Years',

     if(today()-Due_Date>=1462,'4+ Years'))))) as Years

How do I go about factoring in leap years into the script above.

Kind regards,

Paul.

[Jason_Michaelides · Accepted solution]

Instead off Today()-DueDate <= 365 use Today()<=AddMonths(DueDate,12) as this will include the leap years for you. Or I think there is an AddYears(DueDate,1) as well.

Hope this helps,

Jason

[#3]

ive used the following script which works very well :-

if(Today()<=AddMonths(Due_Date,12),'0-1 Years',

     if(today()>=addmonths(Due_Date,13) and today()<=addmonths(Due_Date,24),'1-2 Years',

     if(today()>=addmonths(Due_Date,25) and today()<=addmonths(Due_Date,36),'2-3 Years',

     if(today()>=addmonths(Due_Date,37) and today()<=addmonths(Due_Date,48),'3-4 Years',

     if(Today()>=AddMonths(Due_Date,49),'4+ Years'))))) as Years,