Skip to main content
New Idea

DateDiff should be able to be negative

Related products:None
  • March 6, 2018
  • 10 replies
  • 152 views
seth
Forum|alt.badge.img+9
  • seth
  • Gainsight Employee ⭐️⭐️⭐️
I'm using the new DateDiff function in a Formula Field for the first time.

I expected that, if the DateDiff's "start date" was earlier than the "end date", that the result would be a positive number. And, if it were the reverse, it would be a negative number.

Instead, I'm finding that it's taking the number of days between the days, regardless of which is before the other. This isn't a blocker, since I can use Advanced Logic to combine it with a criteria for "End Date field is greater than Start Date field", but isn't intuitive to me. Maybe I'm just jaded from working with too many datediff functions in other contexts.

Here you can see the DateDiff function producing positive values regardless of which of the dates is earlier:

AI Transformation @ Gainsight

    10 replies

    scotty
    Forum|alt.badge.img
    • scotty
    • Contributor ⭐️⭐️⭐️⭐️⭐️
    • March 6, 2018
    We have datediff? Sweet!
    seth
    Forum|alt.badge.img+9
    • seth
    • Author
    • Gainsight Employee ⭐️⭐️⭐️
    • March 6, 2018
    haha yessir!
    AI Transformation @ Gainsight
    jitin_mehndiratta
    Hi Seth,

    It was designed in this way to find out the number of days between two dates. But I agree that a +ive or -ive sign will help reduce an additional check to identify which date is prior. We plan to enhance the date diff function to be able to return the number of months, years also in addition to number of days. Timelines are not yet known. This change can be taken care of along with these enhancements.

    Regards,
    Jitin 
    sai_ram
    Forum|alt.badge.img+1
    • sai_ram
    • Expert ⭐️⭐️
    • August 7, 2018
    Hi Seth, 

    Since this is working as expected changing this to an enhancement request.This is a valid use-case and we have added this to our road-map.

    Thanks for posting.
    scotty
    pavan_bandi
    Forum|alt.badge.img
    • pavan_bandi
    • Gainsight Employee ⭐️
    • September 12, 2018
    Hi Seth,

    We have added ability in DateDiff function to return positive / negative values. Going forward(October release) you will observe a new checkbox that is added in DateDiff function with label "Get signed value", once this is checked you receive the Signed value instead of absolute value.

    Please find the attached screenshot for reference.



    FYI, We have also added Rule date as one of the argument in DateDiff function.

    Hope this helps!
    scotty
    kate_green
    ankit_jain
    scotty
    Forum|alt.badge.img
    • scotty
    • Contributor ⭐️⭐️⭐️⭐️⭐️
    • September 12, 2018
    Nice! The date diff from run date was exactly what I was going to ask for next! I had come up with some byzantine workaround that can now be deprecated with pleasure. 
    kate_green
    Forum|alt.badge.img
    • kate_green
    • Helper ⭐️⭐️
    • September 12, 2018
    Awesome to both! I needed to use Rule Date earlier this month and was going to put in an enhancement request!
    sai_ram
    Forum|alt.badge.img+1
    • sai_ram
    • Expert ⭐️⭐️
    • October 16, 2018
    Hi All, 

    Negative results in Date Diff function is available in October release.

    Date Diff function subtracts the start datetime field value from the end datetime field and it returns the difference in number of days. Previously, even if the end datetime field had a lower value than start datetime field, the result (number of days) was converted to a positive value. Now, the Get Signed Value checkbox is introduced. If you select the checkbox, negative values are not converted to positive values in the result.

    Please refer the release notes for more information. 

    Thanks for posting!​
    kate_green
    seth
    spencer_engel
    Forum|alt.badge.img+5
    • spencer_engel
    • Expert ⭐️
    • March 17, 2022

    Apparently this positive/negative thing was not implemented in Data Designer, which is turning into a blocker for me. The bottom row should be -1 based on the two rows above it. See below: 

     

     

    rakesh
    Forum|alt.badge.img+2
    • rakesh
    • Lets put your data to work!
    • March 18, 2022

    Hi @spencer_engel  you should be able to see Get Signed value checkbox in Data Designer also. If you do not see it, do raise a bug. 

    Terms & ConditionsCookie settingsAccessibility statement