Forum Discussion
LauraDiak
5 years agoQrew Trainee
Thank you, Don.
I was able to get the first part working. How would you suggest I set up the formula for the second part?
------------------------------
Laura Diak
------------------------------
I was able to get the first part working. How would you suggest I set up the formula for the second part?
------------------------------
Laura Diak
------------------------------
DonLarson
5 years agoQrew Elite
Laura,
You need two tools, conversions and whole set of QB Formulas called Rounding and Truncating
You can use these to break up the Duration into smaller pieces that can then be displayed as Years, Months and Days. However we should make two assumptions
The easiest part is how many total days someone has been there.
When Total Days goes over 365 we have our first year. I would do this going backwards from year to month to day
var number TotalNumDays = ToDays([Duration]);
var number TotalNumYears = $TotalNumDays/365; // Quick Base does not have a function ToYears() which is a bummer, so we made one
//Calculate Years to Display
var number DisplayYears = (Floor($TotalNumYears));
//Calculate Months to Display
var number DisplayMonths= Floor(($TotalNumYears-$DisplayYears)*12);
// Calculate Days to Display
var number DisplayDays= $TotalNumDays-$DisplayYears*365 - $DisplayMonths*31;
//Display
ToText($DisplayYears) & " Years " & "<br>" & ToText($DisplayMonths) & " Months" & "<br>" & ToText($DisplayDays) & " Days"
Play with it and you will get some text output.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
You need two tools, conversions and whole set of QB Formulas called Rounding and Truncating
You can use these to break up the Duration into smaller pieces that can then be displayed as Years, Months and Days. However we should make two assumptions
- All years are 365 days long
- All months are 31 days long
The easiest part is how many total days someone has been there.
- ToDays([Duration])
When Total Days goes over 365 we have our first year. I would do this going backwards from year to month to day
var number TotalNumDays = ToDays([Duration]);
var number TotalNumYears = $TotalNumDays/365; // Quick Base does not have a function ToYears() which is a bummer, so we made one
//Calculate Years to Display
var number DisplayYears = (Floor($TotalNumYears));
//Calculate Months to Display
var number DisplayMonths= Floor(($TotalNumYears-$DisplayYears)*12);
// Calculate Days to Display
var number DisplayDays= $TotalNumDays-$DisplayYears*365 - $DisplayMonths*31;
//Display
ToText($DisplayYears) & " Years " & "<br>" & ToText($DisplayMonths) & " Months" & "<br>" & ToText($DisplayDays) & " Days"
Play with it and you will get some text output.
------------------------------
Don Larson
Paasporter
Westlake OH
------------------------------
- LauraDiak5 years agoQrew TraineeIt worked! Thank you so very much!
Happy New Year!
------------------------------
Laura Diak
------------------------------