Skip to main content

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

Hello Group Members,

 
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