This example illustrates how to calculate the number of days that have elapsed between the order date and today.

Function:

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:

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

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:

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:

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:

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