EXAMPLE  DATEDIF Function
This example illustrates how to calculate the number of days that have elapsed between the order date and today.
Function:
Item  Description 

DATEDIF Function  Calculates the difference between two valid date values for the specified units of measure. 
TODAY Function  Derives the value for the current date in UTC time zone. You can specify a different time zone by optional parameter. 
IF Function  The 
Source:
For the orders in the following set, you want to charge interest for those ones that are older than 90 days.
OrderId  OrderDate  Amount 

1001  1/31/16  1000 
1002  11/15/15  1000 
1003  12/18/15  1000 
1004  1/15/16  1000 
Transformation:
The first step is to create a column containing today's (03/03/16) date value:
Transformation Name 


Parameter: Formula type  Single row formula 
Parameter: Formula  TODAY() 
Parameter: New column name  'Today' 
You can now use this value as the basis for computing the number of elapsed days for each invoice:
Transformation Name 


Parameter: Formula type  Single row formula 
Parameter: Formula  DATEDIF(OrderDate, Today, day) 
The age of each invoice in days is displayed in the new column. Now, you want to add a little bit of information to this comparison. Instead of just calculating the number of days, you could write out the action to undertake. Replace the above with the following:
Transformation Name 


Parameter: Formula type  Single row formula 
Parameter: Formula  IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action') 
Parameter: New column name  'TakeAction' 
To be fair to your customers, you might want to issue a notice at 45 days that the invoice is outstanding. You can replace the above with the following:
Transformation Name 


Parameter: Formula type  Single row formula 
Parameter: Formula  IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action')) 
Parameter: New column name  'TakeAction' 
By using nested instances of the IF
function, you can generate multiple results in the TakeAction
column.
For the items that are over 90 days old, you want to charge 5% interest. You can do the following:
Transformation Name 


Parameter: Columns  Amount 
Parameter: Formula  IF(TakeAction == 'Charge interest',Amount * 1.05,Amount) 
The above sets the value in the Amount
column based on the conditional of whether the TakeAction
column value is Charge interest
. If so, apply 5% interest to the value in the Amount
column.
Results:
OrderId  OrderDate  Amount  Today  TakeAction 

1001  1/31/16  1000  03/03/16  no action 
1002  11/15/15  1050  03/03/16  Charge interest 
1003  12/18/15  1000  03/03/16  Send letter 
1004  1/15/16  1000  03/03/16  Send letter 