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).
Thanks for your help.
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.
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