Date: Thu, 9 Dec 2021 07:15:12 +0000 (GMT) Message-ID: <1779271629.88243.1639034112912@9c5033e110b2> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_88242_1743280039.1639034112912" ------=_Part_88242_1743280039.1639034112912 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html EXAMPLE - DATEDIF Function

# EXAMPLE - DATEDIF Function

This example illustrates how to use the `DATEDIF` fun= ction to calculate the number of days that have elapsed between the order d= ate and today for purposes of informing the customer.

Source:

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

=20 =20 =20 =20
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:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` `TODAY()` `'Today'`
=20

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

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 = =20 =20
Transformation Name <= code>New formula `Single row formula` `DATEDIF(OrderDate, Today, day)`
=20

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 jus= t calculating the number of days, you could write out the action to underta= ke. Replace the above with the following:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` ```IF((DATEDIF(OrderDate, Today, day) > 90= ),'Charge interest','no action')``` `'TakeAction'`
=20

To be fair to your customers, you might want to issue a notice at 45 day= s that the invoice is outstanding. You can replace the above with the follo= wing:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name <= code>New formula `Single row formula` ```IF(DATEDIF(OrderDate, Today, day) > 90,= 'Charge interest',IF(DATEDIF(OrderDate, Today, day) > 45),'Send letter',= 'no action'))``` `'TakeAction'`
=20

By using nested instances of the `IF` function, you can gener= ate 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:

=20
=20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20 =20
Transformation Name `Edit column with formula` `Amount` ```IF(TakeAction =3D=3D 'Charge interest',Amo= unt * 1.05,Amount) ```
=20

The above sets the value in the `Amount` column based on the = conditional of whether the `TakeAction` column value is ```Ch= arge interest```. If so, apply 5% interest to the value in the ```Am= ount``` 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
------=_Part_88242_1743280039.1639034112912--