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
    

Content aside

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