Replicate Excel "bug" in DATEDIF() when calculating service periods using DateTimeDiff()

[Paul_s_Moody]

Hi,

I'm using a workflow to replicate an Excel spreadsheet that is (as well as much more) calculating the many service periods of members of a pension scheme.

The current Excel spreadsheet is using the "backward compatable" formula DATEDIF(...) in order to calculate service periods.

However it is using a "bug" in the formula that if the start date is larger than the end date, an #NUM! error will result. This is then error trapped.

So in the example below, the member joined in 1991 and left in 1999 so didn't work for the company in the 89 to 90 period so Excel results in a 0 value.

[89 to 90 Service]=IFERROR(DATEDIF(MAX([DJS],DATE(1989,10,1)),MIN(DATE(1990,5,17),[DOL]),"D"),0)

     =IFERROR(DATEDIF(MAX(01/11/1991,DATE(1989,10,1)),MIN(DATE(1990,5,17),30/9/1999),"D"),0)
     =IFERROR(DATEDIF(1/11/1991,17/05/1990),"D"),0)
     =IFERROR(!#NUM,"D"),0)
     =0

However, Alteryx doesn't seem to have this problem, and when I use the formula below I get an answer of 533, which is the mathematical day count between the dates...

[89 to 90 Service]=DateTimeDiff(Max([DJS],'1989-10-01'),Min('1990-05-17',[DOL]),"days")
     =533

How do I adjust the formula in Alteryx to replicate formula allowing for the "bug" in Excel?

I've attached a very cutdown workflow with 2 of the relevant formula included.

[Paul_s_Moody]

The best approach I've come up with is....

IIF(Max([DJS],'1989-10-01')>Min('1990-05-17',[DOL]),0,
DateTimeDiff(Max([DJS],'1989-10-01'),
Min('1990-05-17',[DOL]),
"days"))

but not sure how efficient/stable the approach will be!

[Raj · Accepted solution]

In order to replicate the behavior of the Excel formula that takes into account the "bug" in DATEDIF, you can modify the Alteryx formula to include a condition to check if the start date is larger than the end date. If the start date is larger, you can set the result to 0. Here's an example of how you can adjust the formula in Alteryx:

[89 to 90 Service] = IF [DJS] > [DOL] THEN 0 ELSE DateTimeDiff(Max([DJS], '1989-10-01'), Min('1990-05-17', [DOL]), "days") ENDIF

This formula first checks if the start date ([DJS]) is larger than the end date ([DOL]). If it is, it sets the result to 0. Otherwise, it calculates the date difference using DateTimeDiff function, taking into account the adjusted start and end dates.

[Paul_s_Moody]

Thank you!

I've found that I also need to include a ABS(...) function as some of the day counts were being reported as negative.

So the final formula becomes:

IIF(Max([DJS],'1989-10-01')>Min('1990-05-17',[DOL]),0,
ABS(
DateTimeDiff(Max([DJS],'1989-10-01'),
Min('1990-05-17',[DOL]),
"days"))
)