Arriving at the Difference in Two Dates in IDEA Vs MS-Excel

 

We routinely have to compute the difference in two dates in a database as a measure for operational effectiveness or to perform bucket analysis (aging) or to calculate interest on delays and more.

 

The MS-Excel way to arrive at the difference in two dates is to perform a Date 1 - Date 2 to arrive at the difference in dates in a new field in Excel.

 

In IDEA we do it differently using an @Function.

We append a virtual numeric field with the function @age(Date1, Date2) where Date 1 is the Later Date and Date 2 is the Earlier Date.

 

So to calculate the difference in Collection Date and Bill Date for interest levy on delays we do this by appending a virtual numeric field titled 'Collection Lag' with the criteria @Age(Collection Date, Bill Date)

 

Just as a side - bar topic for discussion if we need to convert this difference in days to say difference in month/s we can tweak the criteria above from @Age(Collection Date, Bill Date) to @Age(Collection Date, Bill Date)/30

 

In the same example being discussed if you need to apply the criteria - @Age(Collection Date, Bill Date) through a Direct Extraction to filter out records where say Collection Date is prior to Bill Date which is an abnormal state the criteria in the Direct Extraction will be @Age(Collection Date, Bill Date) < 0 to capture all negative differences.

 

Please note this criteria - @Age(Collection Date, Bill Date) < 0 will be applied only through an extraction and not a field append to get a list of negative differences.

 

Regards

 

Group Admin Team