Getting the Year & Month between two dates

Halio
Halio
5 yrs ago
19replies

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"

- Halio
- Halio
- 5 yrs ago
- Reported - view
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
- Nick
- 5 yrs ago
- Reported - view
Nioxus has a global function for this

Get it here (you have to create a free account)

https://www.nioxus.com/portal/post/275
- Halio
- Halio
- 5 yrs ago
- Reported - view
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?
- Nick
- 5 yrs ago
- Reported - view
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
- Halio
- Halio
- 5 yrs ago
- Reported - view
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.
- Watch These Kids Bloom, Inc.
- Jen
- 5 yrs ago
- Reported - view
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!
- Nick
- 5 yrs ago
- Reported - view
Thanks for sharing Jen!
- Halio
- Halio
- 5 yrs ago
- Reported - view
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.
- Watch These Kids Bloom, Inc.
- Jen
- 5 yrs ago
- Reported - view
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.
- Nick
- 5 yrs ago
- Reported - view
@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
- Halio
- Halio
- 5 yrs ago
- Reported - view
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.
- Halio
- Halio
- 5 yrs ago
- Reported - view
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.
- Nick
- 5 yrs ago
- Reported - view
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.
- Halio
- Halio
- 5 yrs ago
- Reported - view
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.
- Jorg
- 5 yrs ago
- Reported - view
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
- Watch These Kids Bloom, Inc.
- Jen
- 5 yrs ago
- Reported - view
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!
- Jorg
- 5 yrs ago
- Reported - view
Hi Jen,

I have it uploaded now. It is the datbase "102_Age in years-months-days".

Best, Jörg
- Halio
- Halio
- 5 yrs ago
- Reported - view
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.
- aceldamared
- 4 mths ago
- Reported - view
Buenas tardes a todos, comparto un algoritmo para calcular la edad en años, meses y dias:

let fechaNacimiento := FechaNacimiento;
let hoy := today();

let years := year(hoy) - year(fechaNacimiento);
let months := month(hoy) - month(fechaNacimiento);
let days := day(hoy) - day(fechaNacimiento);

if days < 0 then
months := months - 1;
days := days + (day(today()) - day(date(year(today()), month(today()), 1)));
end;

if months < 0 then
years := years - 1;
months := months + 12;
end;

let edad := years + " años, " + months + " meses y " + days + " días";
edad

Espero les ayude mucho.

Content aside

4 mths agoLast active
19Replies
4056Views
1 Following

Getting the Year & Month between two dates

19 replies

Content aside

Overview