Datediff results displaying -1 when dates are the same | Community
Skip to main content
LaramieNewbs
LaramieNewbs
Level 2
November 1, 2024
Solved

Datediff results displaying -1 when dates are the same

  • November 1, 2024
  • 2 replies
  • 696 views

I have a calculated field on a custom form to calculate the days between a custom date field on a project and the planned completion date of that project. However, when those two dates are the same, the formula is showing a result of -1. I have attempted rounding, adding a day, and setting an if statement to display "0" if the dates are the same but I am not having any luck with those. They either aren't working at all (presumably a syntax error) or they are displaying inconsistent results. 

 

My goal is to have a number of days, rounded to the nearest whole number. But, for it to display "1" if the difference is truly one day, "0" if it's the same day, etc. 

 

Here is the calculation that I am currently working with: 

ROUND(DATEDIFF({DE:TM10 Date - Approved},{plannedCompletionDate}),IF({DE:TM10 Date - Approved}={plannedCompletionDate},"0"))
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by skyehansen

The calculated data expressions page has a list of functions you can try -- I'm wondering if maybe you have tried CLEARTIME.

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/calculated-data-expressions

 

If your custom field is just a date field and not time field, you could maybe just go with DATEDIFF({DE:TM10 Date - Approved},CLEARTIME({plannedCompletionDate}))

 

2 replies

skyehansen
Community Advisor and Adobe Champion
skyehansenCommunity Advisor and Adobe ChampionAccepted solution
November 1, 2024

The calculated data expressions page has a list of functions you can try -- I'm wondering if maybe you have tried CLEARTIME.

https://experienceleague.adobe.com/en/docs/workfront/using/reporting/reports/calculated-custom-data/calculated-data-expressions

 

If your custom field is just a date field and not time field, you could maybe just go with DATEDIFF({DE:TM10 Date - Approved},CLEARTIME({plannedCompletionDate}))

 

    Rafal_Bainie
    Community Advisor
    Rafal_BainieCommunity Advisor
    Community Advisor
    November 5, 2024

    I don't know what is wrong with DATEDIFF or how exactly it is different from WEEKDAYDIFF.

    But the concept you're trying to capture is valid. Here is formula I use with success:

     

    IF(ISBLANK({parent}.{DE:Confirmed Deadline}),"No Value",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=0,"Same day",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=1,"Next day",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=2,"Due in 2 days",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=3,"Due in 3 days",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=4,"Due in 4 days",IF(WEEKDAYDIFF({DE:Entry DateTime},{parent}.{DE:Confirmed Deadline})=5,"Due in 5 days","Due in more than 5 days")))))))

     

    there are some differences: 

    - I'm displaying result as text, not number

    - I don't use ROUND

    I think there is more difference between DATEDIFF and WEEKDAYDIFF than just the fact that the latter ignores weekends. I did try to update my formula to DATEDIFF, but it stops working correctly.

    This isn't answer to the question but maybe will help to guide you to the correct answer

    good luck!

    Rafal

    Terms & ConditionsAccessibility statement

    Loading footer...