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

Thanks for your help.

3 replies

null
    • 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
Login to reply

Content aside

  • Status Answered
  • 1 yr agoLast active
  • 3Replies
  • 111Views
  • 3 Following