
Getting the Year & Month between two dates
I created a convoluted formula to calculate the age of a student at the start of a course. The basic idea is 'Course Start Date' - 'Student Birthdate'
I was hoping someone could give me some pointers how to simplify my formula as I have a hard time believing this is an efficient way of doing this.
year(Classes.Terms.'Term Start Date') - year(Students.Birthdate) - if month(Classes.Terms.'Term Start Date') < month(Students.Birthdate) then
1
else
if month(Classes.Terms.'Term Start Date') = month(Students.Birthdate) and day(Classes.Terms.'Term Start Date') < day(Students.Birthdate) then
1
else
0
end
end + "y " + if month(Classes.Terms.'Term Start Date') < month(Students.Birthdate) and day(Classes.Terms.'Term Start Date') >= day(Students.Birthdate) then
month(Classes.Terms.'Term Start Date') + 12 - month(Students.Birthdate)
else
if month(Classes.Terms.'Term Start Date') > month(Students.Birthdate) and day(Classes.Terms.'Term Start Date') < day(Students.Birthdate) then
month(Classes.Terms.'Term Start Date') - 1 - month(Students.Birthdate)
else
if month(Classes.Terms.'Term Start Date') < month(Students.Birthdate) and day(Classes.Terms.'Term Start Date') < day(Students.Birthdate) then
month(Classes.Terms.'Term Start Date') + 11 - month(Students.Birthdate)
else
month(Classes.Terms.'Term Start Date') - month(Students.Birthdate)
end
end
end + "m"
-
I'm using this formula (calculates days too)
---
let D1 := if TermStartDate > Birthdate then
Birthdate
else
TermStartDate
end;
let D2 := if TermStartDate > Birthdate then
TermStartDate
else
TermStartDate
end;
let DD := if day(D2) >= day(D1) then
day(D2) - day(D1)
else
day(date(year(D1), month(D1) + 1, 1) - 1) - day(D1) + day(D2)
end;
let MM := if month(D1) <= month(D2) then
month(D2) - month(D1)
else
12 - month(D1) + month(D2)
end;
let MMM := if day(D2) >= day(D1) then MM else MM - 1 end;
let YY := if month(D2) > month(D1) then
year(D2) - year(D1)
else
if month(D2) < month(D1) then
year(D2) - year(D1) - 1
else
if day(D1) <= day(D2) then
year(D2) - year(D1)
else
year(D2) - year(D1) - 1
end
end
end;
if TermStartDate = null then
""
else
if YY = 0 then
""
else
if YY = 1 then
YY + " year" + if MMM = 0 and DD = 0 then "" else ", " end
else
YY + " years" + if MMM = 0 and DD = 0 then "" else ", " end
end
end + if MMM = 0 then
""
else
if MMM = 1 then
MMM + " month" + if DD = 0 then "" else ", " end
else
MMM + " months" + if DD = 0 then "" else ", " end
end
end + if DD = 0 then
""
else
if DD = 1 then DD + " day" else DD + " days" end
end
end -
That is way cleaner than what I made but I am getting some wrong ages. One student was born on Sep 20, 2007 & the class started on Sep 9, 2019 and your function shows 11 years, -1 months, 19 days while my ugly attempt gives the correct 11y 11m. I could find why this is happening yet. I assume the 12 months are not added in this case. Can you see why, Nick?
I'll post back when I can find the error.
-
I wrote the following code in a formula field to show the years and months between two dates where those two dates were date fields in my table. You are welcome to adapt it. First you have to define the variables ADate and DDate from other fields in your table.
let xDays := floor(days(ADate, DDate));
let xYears := floor(days(ADate, DDate) / 365.25);
let xExtraDays := xDays - floor(xYears * 365.25);
let xMonths := floor(xExtraDays / 30.4167);
xYears + " years " + xMonths + " months"Note that it assumes the number of days in a year to be 365.25 to account for leap year every four years. And it further assumes there to be an average of 30.4167 days in a month. This formula
prove
d accurate when I tested it against an online date calculator. Perhaps it will help you. Good luck! -
Nice short version Jen! I quickly zipped through to compare your formula, Nicks, and my own and as I mentioned there are some problem with Nicks which I am trying to find because I like the elegance of it and it should be 100% accurate and when the day and month are the same then your month is off by 1. I assume that is because you took an average for the length of month and year which cannot account for this accurately. Also your formula wouldn't know which year is a leap year which would result in a marginally off result.
-
Hmmm. I was aware that unless a lot of years were involved it was taking a chance to include an allowance for leap years. But I thought that, other than that, it was accurate. So thanks for the heads up! Sounds like my formula needs a review. I wish you the best in resolving this problem and look forward to seeing the best solution.
-
@Halio,
pls try this version
---
let D1 := if Birthdate > TermStartDate then
TermStartDate
else
Birthdate
end;
let D2 := if Birthdate > TermStartDate then
Birthdate
else
TermStartDate
end;
let DD := if day(D2) >= day(D1) then
day(D2) - day(D1)
else
day(date(year(D1), month(D1) + 1, 1) - 1) - day(D1) + day(D2)
end;
let MM := if month(D1) < month(D2) then
month(D2) - month(D1)
else
12 - month(D1) + month(D2)
end;
let MMM := if day(D2) >= day(D1) then MM else MM - 1 end;
let YY := if month(D2) > month(D1) then
year(D2) - year(D1)
else
if month(D2) < month(D1) then
year(D2) - year(D1) - 1
else
if day(D1) <= day(D2) then
year(D2) - year(D1)
else
year(D2) - year(D1) - 1
end
end
end;
if Birthdate = null then
""
else
if YY = 0 then
""
else
if YY = 1 then
YY + " year" + if MMM = 0 and DD = 0 then "" else ", " end
else
YY + " years" + if MMM = 0 and DD = 0 then "" else ", " end
end
end + if MMM = 0 then
""
else
if MMM = 1 then
MMM + " month" + if DD = 0 then "" else ", " end
else
MMM + " months" + if DD = 0 then "" else ", " end
end
end + if DD = 0 then
""
else
if DD = 1 then DD + " day" else DD + " days" end
end
end