DATEDIFF, DECIMALTOTIME, TIMETODECIMAL

  • Resolved vkarpov

    (@vkarpov)


    2 years, 9 months ago

    I want to sum two time periods which I pre-calculate using DATEDIFF and also format conversion.

    However, I cannot get the desired result in the year, month, day format.

    (I already looked at you here –link-, but I couldn’t do it)

    Here are some code inserts to understand the question

    fieldname17:
    (function(){var o = DATEDIFF( fieldname3, fieldname2, “dd.mm.yyyy”, “y”);
    return CONCATENATE(o[“years”], ” “, o[“months”], ” “, o[“days”]);
    })()

    fieldname18:
    (function(){var o = DATEDIFF( fieldname13, fieldname12, “dd.mm.yyyy”, “y”);
    return CONCATENATE(o[“years”], ” “, o[“months”], ” “, o[“days”]);
    })()

    DECIMALTOTIME(SUM(TIMETODECIMAL(fieldname17,”dd.mm.yyyy”,”y”),TIMETODECIMAL(fieldname18,”dd.mm.yyyy”,”y”)),”y”,”y,m,d”)

Viewing 2 replies - 1 through 2 (of 2 total)
  • Plugin Author codepeople

    (@codepeople)

    2 years, 9 months ago

    Hello @vkarpov

    Thank you very much for using our plugin.

    Code like the following one returns values with format yyyy mm dd and not dd.mm.yyyy:

    return CONCATENATE(o['years'], ' ', o['months'], ' ', o['days']);

    Furthermore, I recommend using the raw fields’ values in the last equation. Also, you should transform the values to the lower measurement unit (days) to avoid accuracy issues.

    DECIMALTOTIME(SUM(TIMETODECIMAL(fieldname17|r, 'y m d', 'd'),TIMETODECIMAL(fieldname18|r, 'y m d', 'd')), 'd', 'y,m,d')

    Best regards.

    Thread Starter vkarpov

    (@vkarpov)

    2 years, 9 months ago

    Thank you very much! Everything works great!

    And thanks for your plugin, we’ve been using it for several years!

Viewing 2 replies - 1 through 2 (of 2 total)

The topic ‘DATEDIFF, DECIMALTOTIME, TIMETODECIMAL’ is closed to new replies.