##### Page tree

Release 8.2.1

Contents:

This example illustrates how to use the `DATEDIF` function to calculate the number of days that have elapsed between the order date and today for purposes of informing the customer.

Source:

For the orders in the following set, you want to charge interest for those ones that are older than 90 days.

OrderIdOrderDateAmount
10011/31/161000
100211/15/151000
100312/18/151000
10041/15/161000

Transformation:

The first step is to create a column containing today's (03/03/16) date value:

Transformation Name `New formula` `Single row formula` `TODAY()` `'Today'`

You can now use this value as the basis for computing the number of elapsed days for each invoice:

Transformation Name `New formula` `Single row 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 `New formula` `Single row formula` `IF((DATEDIF(OrderDate, Today, day) > 90),'Charge interest','no action')` `'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 `New formula` `Single row formula` `IF(DATEDIF(OrderDate, Today, day) > 90,'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter','no action'))` `'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 `Edit column with formula` `Amount` `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:

OrderIdOrderDateAmountTodayTakeAction
10011/31/16100003/03/16no action
100211/15/15105003/03/16Charge interest
100312/18/15100003/03/16Send letter
10041/15/16100003/03/16Send letter