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 believe I missed an equal sign in this section:
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 -
Nioxus has a global function for this
Get it here (you have to create a free account)
-
Thank you Nick!
They used it as a global function. I'm trying to use it in a specific table and checking the time difference between students's birthdate and the start of a class. I tried to customize Nioxus' code but haven't figured out yet how to accomplish this. Do you have any pointers?
-
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
proved accurate when I tested it against an online date calculator. Perhaps it will help you. Good luck! -
Thanks for sharing Jen!
-
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 -
Nick, whatever you changed fixed now all minus errors but there is one case that I saw where a student's age shows 6 years 12 months 4 days while your previous function had it correct with 6y 4d as the student's birthdate is 5 Sep 2013.
-
Nick, I couldn't find the part that causes 12 months to be displayed for that one student when it should be 0 months. I'm unfortunately still not practiced enough to debug Ninox scripts like yours.
-
Same here Halio :-(
This is not my code. I'm not a programmer, just test and test and then test again...
I can't remember where I found it, maybe in some database from Ninox folks.
Let's hope some guru do the debuging.
-
Oh I had assumed you came up with it. I ran into this problem as well with copying code and forgetting where I took it from. Since then I started a database to track the code snippets with links to the sources.
-
Hi Halio,
I have sent you a sample database that has the possibilty included to calculate the precise age or a time periode in years, months and days.
Best, Jörg
-
Jörg,
Is the sample in the webinar database? If so, which item is it? I would like to look at this as well.
Thank you! -
Hi Jen,
I have it uploaded now. It is the datbase "102_Age in years-months-days".
Best, Jörg
-
Jörg, the file you Shared is golden! Thank you so much.
I am collecting everything I use for myself but it makes me wonder about new users and how the barrier of entry is high when someone has to find all these great pre-made functions.