View Only
Expand all | Collapse all

DateDiff in work days

Jump to Best Answer
  • 1.  DateDiff in work days

    Posted Jul 09, 2014 07:43 AM

    What would be your suggestion for solving the following issue: getting two date difference in workdays.


    Currently i see two parts of this question

    1. How to get date difference in working days at all in Clarity (Calculated attribute datediff gives the difference in calendar days)?
    2. How to get date difference so it takes into account user basecalendar?

    Second issue is not so crucial/releavant if there would be any reasonable way to add attribute to an object, that shows difference in working days.

  • 2.  Re: DateDiff in work days
    Best Answer

    Posted Jul 09, 2014 08:55 AM

    Sorry, I don't have a smart answer to #1 or #2 (apart from "write a clever database routine"), but I do have an answer that could build upon "write a clever database routine" that would help you place the output of that clever database routine on a Clarity attribute;


    TIP : How To Put Any Dynamically Generated Value On A Clarity Object

  • 3.  Re: DateDiff in work days

    Posted Jul 09, 2014 09:12 AM

  • 4.  Re: DateDiff in work days

    Posted Jul 09, 2014 09:20 AM

    ^ that falls into the category of "write a clever database routine" (similar for Oracle solutions - can just google for those as well) but you then still have the problem of displaying in the application - hence the "tip" 

  • 5.  Re: DateDiff in work days

    Posted Jul 11, 2014 04:46 AM

    Thnx, managed to get it working nicely in project properties page - now shows the date difference in workdays there.


    But also a problem - when i'm adding this new field to projects list page, it shows always value 0. Any ideas/suggestions?



  • 6.  Re: DateDiff in work days

    Posted Jul 11, 2014 04:51 AM

    Yeah, the "technique" in that tip just doesn't work in LIST views   (I think that is mentioned in the document a few times)

  • 7.  Re: DateDiff in work days

    Posted Jul 11, 2014 04:55 AM

    Oh, yeah - missed that in a document . Anyway, thanks.


    And this "technique" also seems not working in 13.3 - could not select Object Id for the parameter mappings section in Object Attribute Id - there wasn't any object id (got this working fine in 13.2)

  • 8.  Re: DateDiff in work days

    Posted Jul 11, 2014 05:19 AM

    Ouch, I'll be very upset if this doesn't work in 13.3 (I have not investigated, you have me a little worried though )

  • 9.  Re: DateDiff in work days

    Posted Jul 11, 2014 07:45 AM

    We prototyped Dave's wizardry as well - fantastic stuff - but the List View caveat was a show stopper for us. We ended up going with read only attributes & stored procedures to update the value. This has worked well for us. There's a little trick to creating read-only attributes with NULL values that I can share if interested in going down this path. The Idea to just have this capability OOTB can be found here: Allow NULL Read Only Attributes. Please vote this up if you think it'll provide your organization value.

  • 10.  Re: DateDiff in work days

    Posted Jul 21, 2014 08:42 AM

    Dave wrote:


    Ouch, I'll be very upset if this doesn't work in 13.3 (I have not investigated, you have me a little worried though )

    I have just looked in a 13.3 system where that "technique" is used ; and it is all working fine there.

  • 11.  Re: DateDiff in work days

    Posted Jul 22, 2014 10:07 PM

    Just to toss in a non “write a clever database routine”:


    <?xml version="1.0" encoding="utf-8"?>

    <gel:script xmlns:core="jelly:core"





        xmlns:sql="jelly:sql" xmlns:xog=""






        <gel:parseDate dateVar="startDate" format="yyyy-MM-dd">2014-06-09</gel:parseDate>

        <gel:parseDate dateVar="endDate" format="yyyy-MM-dd">2014-06-23</gel:parseDate>


        <core:getStatic className="java.util.Calendar" field="SUNDAY" var="sunDay" />

        <core:getStatic className="java.util.Calendar" field="MONDAY" var="monDay" />

        <core:getStatic className="java.util.Calendar" field="DAY_OF_WEEK" var="dayOfWeek" />


        <core:invokeStatic className="java.util.GregorianCalendar" method="getInstance" var="startCalendar" />


        <core:invoke method="setTime" on="${startCalendar}">

            <core:arg type="java.util.Date" value="${startDate}"/>



        <core:invoke method="get" on="${startCalendar}" var="startDayOfWeek">

            <core:arg type="java.lang.Integer" value="${dayOfWeek}"/>



        <core:invoke method="add" on="${startCalendar}">

            <core:arg type="java.lang.Integer" value="${dayOfWeek}"/>

            <core:arg type="java.lang.Integer" value="${-startDayOfWeek+1}"/>



        <core:invokeStatic className="java.util.GregorianCalendar" method="getInstance" var="endCalendar" />


        <core:invoke method="setTime" on="${endCalendar}">

            <core:arg type="java.util.Date" value="${endDate}"/>



        <core:invoke method="get" on="${endCalendar}" var="endDayOfWeek">

            <core:arg type="java.lang.Integer" value="${dayOfWeek}"/>




        <core:invoke method="add" on="${endCalendar}">

            <core:arg type="java.lang.Integer" value="${dayOfWeek}"/>

            <core:arg type="java.lang.Integer" value="${-endDayOfWeek+1}"/>



        <core:invoke method="getTimeInMillis" on="${startCalendar}" var="startMillis"/>

        <core:invoke method="getTimeInMillis" on="${endCalendar}" var="endMillis"/>


        <core:set value="${(endMillis-startMillis)/(1000*60*60*24)}" var="days" />

        <core:set value="${days-(days*2/7)}" var="workDays" />

        <gel:log>The number of days = ${days}</gel:log>



        <core:if test="${startDayOfWeek == sunDay}">

            <core:set value="${monDay}" var="startDayOfWeek"/>



        <core:if test="${endDayOfWeek == sunDay}">

            <core:set value="${monDay}" var="endDayOfWeek"/>




        <core:set var="workingDays" value="${workDays-startDayOfWeek+endDayOfWeek}"/>

        <gel:log>The number of working days = ${workingDays}</gel:log>






  • 12.  Re: DateDiff in work days

    Posted Jul 24, 2014 02:05 AM

    To where and/or how this script/xml can be written. Is this entirely back-end solution or can it be entered/modified also in UI?

  • 13.  Re: DateDiff in work days

    Posted Jul 25, 2014 03:01 PM

    I would place this in a gel script (update to get the start / finish dates of the triggered object and the core:invoke to update the date difference value) and run it via a process on an event enabled object.

    The script would update a date difference field base on if either the start or finish date changed.

    As for the base calendar, I didn't see any way to read it via the WSDL and I believe it is a blob and I am not sure where it is kept.




  • 14.  Re: DateDiff in work days

    Posted Jul 28, 2014 08:35 AM

    I should put CA to develop a custom db function to calculate this to be called by any user when needed.

  • 15.  Re: DateDiff in work days

    Posted Jul 28, 2014 03:43 PM

    If I wasn't working in the On-Demand environment, I would most likely code this in Java and wrap it up in the Gel Tag library.




  • 16.  Re: DateDiff in work days

    Posted Jul 09, 2014 11:57 AM

    Here's how we do workdays in Clarity. Since Calendars aren't available in the data model we have a custom object to hold our holidays. Here's the idea to make this available in the app and easy for all to use: Enhanced Access to Base Calendar Features in Clarity. Please vote up the idea if this would provide value to your organization.