2

Duration in days, months and years

I'm fairly new at Ninox. I'm building a research database for history data in which we use a lot of dates, for ages, duration of events, etc. An easy readable display of duration would be great for the usability of the database.

Ninox has a days function, returning the number of days between 2  given dates but no months or years functions. The days function takes leap years into account.

This is my script for a Duration field, working from a Start date field and an End date field. 

Displaying duration in years, months and dates holds some ambiguities because of the difference in length of the various months. See https://en.wikipedia.org/wiki/ISO_8601#Durations if you're interested.

"// startDate and endDate would be the arguments if you'd write a function //";
let startDate := 'Start date';
let endDate := 'End date';
"// Take the dates apart //";
let dS := day(startDate);
let mS := month(startDate);
let yS := year(startDate);
let dE := day(endDate);
let mE := month(endDate);
let yE := year(endDate);
"// dsLargerThandE is necessary for resultDays and resultMonths //";
let dSLargerThandE := dS > dE;
"// tempMonths is necessary for resultMonths and resultYears //";
let tempMonths := if dSLargerThandE then mE - mS - 1 else mE - mS end;
"// Days //";
let resultDays := days(if dSLargerThandE then date(yE, mE - 1, dS) else date(yE, mE, dS) end, 'End date');
"// Months //";
let resultMonths := if tempMonths < 0 then tempMonths + 12 else tempMonths end;
"// Years //";
let resultYears := if tempMonths < 0 then yE - yS - 1 else yE - yS end;
"// Display the results nicely taking singulars and plurals in account //";
let niceDays := if resultDays = 1 then
        text(resultDays) + " day"
    else
        if resultDays > 1 then
            text(resultDays) + " days"
        else
            ""
        end
    end;
let niceMonths := if resultMonths = 1 then
        text(resultMonths) + " month"
    else
        if resultMonths > 1 then
            text(resultMonths) + " months"
        else
            ""
        end
    end;
let niceYears := if resultYears = 1 then
        text(resultYears) + " year"
    else
        if resultYears > 1 then
            text(resultYears) + " years"
        else
            ""
        end
    end;
"// If you concat an array of strings, empty values disappear from the resulting string //";
let resultClean := [niceYears, niceMonths, niceDays];
concat(resultClean)

Cheers! Tips and revisions are welcome!

1 reply

null
    • koeienjam
    • 10 mths ago
    • Reported - view

    Missed a reference directly to the field somewhere

    "// Days //";
    let resultDays := days(if dSLargerThandE then date(yE, mE - 1, dS) else date(yE, mE, dS) end, 'End date');

    should be

    "// Days //";
    let resultDays := days(if dSLargerThandE then date(yE, mE - 1, dS) else date(yE, mE, dS) end, endDate);

Content aside

  • 2 Likes
  • 10 mths agoLast active
  • 1Replies
  • 96Views
  • 1 Following