Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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
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,