Datediff results displaying -1 when dates are the same

[LaramieNewbs]

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"))

[skyehansen · Accepted solution]

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/...

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]

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