# 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 |