DateDiff should be able to be negative

  • 6
  • Idea
  • Updated 4 days ago
  • Implemented
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:

Photo of Seth

Seth, Employee

  • 14,544 Points 10k badge 2x thumb

Posted 7 months ago

  • 6
Photo of Scotty

Scotty, Champion

  • 2,182 Points 2k badge 2x thumb
We have datediff? Sweet!
Photo of Seth

Seth, Employee

  • 14,544 Points 10k badge 2x thumb
haha yessir!
Photo of Jitin Mehndiratta

Jitin Mehndiratta, Product Manager

  • 3,264 Points 3k badge 2x thumb
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 
Photo of Sai Ram Pulluri

Sai Ram Pulluri, Official Rep

  • 13,270 Points 10k badge 2x thumb
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.
Photo of Pavan Bandi

Pavan Bandi, Employee

  • 690 Points 500 badge 2x thumb
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!
Photo of Scotty

Scotty, Champion

  • 2,182 Points 2k badge 2x thumb
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. 
Photo of Kate Green

Kate Green, Champion

  • 5,980 Points 5k badge 2x thumb
Awesome to both! I needed to use Rule Date earlier this month and was going to put in an enhancement request!
Photo of Sai Ram Pulluri

Sai Ram Pulluri, Official Rep

  • 13,270 Points 10k badge 2x thumb
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!​