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

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.

ServiceTest.yxmd

Workflow

Developer

Date Time

Common Use Cases

Accepted answers

Raj

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.

All comments

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

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

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703