0

Convert days to months and years

Hi, I am using the days(date1,date2) function which gives me a result in days.

In some results I have a high number like 550 days which is 18 months or 1 year and 6 months.

I want to know if there is a simple solution to convert the result (550 days) into an easier to understand result (ex: 1 year and 6 months).

3replies

• Fred
• 1 yr ago
• Reported - view

Here is one possibility. The big limitation is that it assumes all months are 30 days long, so it starts to lose accuracy very quickly the further out in time it goes.

You can put this in a formula field:

``````let numDays := days('Start Date', 'End Date');
let numyear := floor(numDays / 365);
let nummonth := if numyear < 1 then
numDays / 30
else
(numDays - numyear * 365) / 30
end;
let printYear := switch true do
case numyear = 1:
"1 year"
case numyear > 1:
numyear + " years"
end;
let printMonth := switch true do
case nummonth < 1:
switch true do
case numyear = 0:
switch true do
case numDays = 1:
"1 day"
case numDays > 1:
numDays + " days"
end
default:
if numDays - numyear * 365 = 0 then
""
else
numDays - numyear * 365 + " days"
end
end
case floor(nummonth) = 1:
floor(nummonth) + " month"
case floor(nummonth) > 1:
floor(nummonth) + " months"
end;
if numyear = 0 then
printMonth
else
if printMonth = "" then
printYear
else
printYear + " and " + printMonth
end
end
``````

Line 1: you will need to change the two field names to match your date fields.

Line 2: finds the number of possible years using the floor() command to round down to the nearest whole number.

Lines 3 - 7: find the number of possible months there are when the number of years is removed.

Lines 8 - 13: figures out if there is the need to show year or years

Lines 14 - 35: figures out if there is the need to show day, days, month, or months

Lines 36 - 44: figures out what to actually display

Hopefully someone can simplify my code.

• Ninox partner
• RoSoft_Steven.1
• 1 yr ago
• Reported - view

My 2 cents:

``````let numdays := days(date1,date2);
let numyears := floor(numdays * 0.0027378507871321013);
let remdays := numdays % 365.25;
let nummonths := floor(remdays * 0.03285420944558522);
let remday := ceil(remdays % 30.4375);
numyears + " years + " + nummonths + " months + " + remday + " day(s)"
``````
• Créateur de bien-être
• Sebastien_Guillet
• 1 yr ago
• Reported - view

Thanks for your feedback. I took the code from  RoSoft_Steven then I adapted it with conditions. I notice that it is not accurate because for a date of January 3, 2018 today, I lose 2 days. It's not a big deal in my case.

Here is an extract of the code that I adapted and which displays only the useful information (in French) and in the form of a sentence by adapting to the singular and the plural. Hoping it can help other people.

``````let numdays := days('Date1', today());
let numyears := floor(numdays * 0.0027378507871321013);
let remdays := numdays % 365.25;
let nummonths := floor(remdays * 0.03285420944558522);
let remday := ceil(remdays % 30.4375);
if numyears > 0 then
numyears + if numyears > 1 then " ans" else " an" end
end +
if nummonths > 0 and numyears > 0 and numyears < 2 then
" et "
end +
if nummonths > 0 and numyears < 2 then
nummonths + " mois"
end +
if numyears = 0 and nummonths = 0 and remday > 0 then
remday + if remday = 1 then " jour" end + if remday > 1 then " jours" end
end +
if numyears = 0 and nummonths = 0 and remday = 0 then
"moins de 24 heures"
end
``````