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