0

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"

18replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Halio
    • Halio
    • Halio
    • 3 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

    Like
    • Nick
    • Nick
    • 3 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

    Like
    • Halio
    • Halio
    • Halio
    • 3 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?

    Like
    • Nick
    • Nick
    • 3 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

    Like
    • Halio
    • Halio
    • Halio
    • 3 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. 

    Like
    • Jen
    • Watch These Kids Bloom, Inc.
    • Jen
    • 3 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!

    Like
    • Nick
    • Nick
    • 3 yrs ago
    • Reported - view

    Thanks for sharing Jen!

    Like
    • Halio
    • Halio
    • Halio
    • 3 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.

    Like
    • Jen
    • Watch These Kids Bloom, Inc.
    • Jen
    • 3 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.

    Like
    • Nick
    • Nick
    • 3 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

    Like
    • Halio
    • Halio
    • Halio
    • 3 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.

    Like
    • Halio
    • Halio
    • Halio
    • 3 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.

    Like
    • Nick
    • Nick
    • 3 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.

    Like
    • Halio
    • Halio
    • Halio
    • 3 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.

    Like
  • 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

    Like
    • Jen
    • Watch These Kids Bloom, Inc.
    • Jen
    • 3 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!

    Like
  • Hi Jen, 

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

    Best, Jörg

    Like
    • Halio
    • Halio
    • Halio
    • 3 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.

    Like
Like Follow
  • 3 yrs agoLast active
  • 18Replies
  • 3995Views