DateDiff should be able to be negative
None
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:
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:
Sign up
If you ever had a profile with us, there's no need to create another one.
Don't worry if your email address has since changed, or you can't remember your login, just let us know at community@gainsight.com and we'll help you get started from where you left.
Else, please continue with the registration below.
Welcome to the Gainsight Community
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.
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
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.
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!
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!
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:
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.