Forum Discussion

Elder22's avatar
Elder22
Frequent Visitor
9 years ago
Solved

DATEDIFF help

Hi,

 

I have searched the forums for the problem I am having and cannot seem to find the exact problem, so hope you can help!

 

I am trying to work out the difference between two dates in months, so I can calculate all opportunities due to close this month, and the next two months.

 

I use this formula:

 

DateDiff = DATEDIFF(Opportunity[Close Date],TODAY(),MONTH)

 

However I get the error message " In DATEDIFF function, the start date cannot be greater than the end date", but I expect this - as some oportunities may be closed in the past we wll as the future. How can I solve this so opportunities can be identified in the next 3 months? Even if past opportunities were negative values, for instance.

 

Any help appreciated - thanks.

Need Help
  • vanessafvg's avatar
    vanessafvg
    9 years ago

    Elder22 so do you want to ignore the opportunity dates that are > than today?

     

    if so its really quite simple, you can ignore it until its relevant

     

     

    DateDiff =
    IF (
        TODAY () > Opportunity[Close Date],
        DATEDIFF ( Opportunity[Close Date], TODAY (), MONTH ),
        0
    )

     

     

     

2 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    9 years ago

    Elder22 so do you want to ignore the opportunity dates that are > than today?

     

    if so its really quite simple, you can ignore it until its relevant

     

     

    DateDiff =
    IF (
        TODAY () > Opportunity[Close Date],
        DATEDIFF ( Opportunity[Close Date], TODAY (), MONTH ),
        0
    )

     

     

     

    • Elder22's avatar
      Elder22
      Frequent Visitor
      9 years ago

      Hello,

       

      Thanks for your response - this worked fine for me, thanks very much I will mark as solved.