Before you begin: There can be difficulty in calculating the days between two date fields in a SmartConnector™, especially when those fields were not designated to store dates. This article shows you how to create a custom calculation that will calculate the difference between two date columns. This article is written for users comfortable with SmartConnectors™ and using calculations. For more information on these topics, please refer to FAQs: SmartConnectors™ and FAQs: Calculations.
Create a Custom Calculation
1] Log in to TapClicks and, on the Data menu, click Calculations.
2] In the top right corner, click the Add New Calculation (plus sign) icon.
3] On the New Calculation page, enter an appropriate name for the calculation in the Name textbox.
4] Select Number from the Data Type dropdown menu.
5] From the dropdown menus, choose the Data Category and Data View that contain the two date fields you would like to calculate the days between.
6] In the calculation builder, enter the following formula:
= TO_DATE ({Close Date}, "MM/DD/YYYY") - TO_DATE ({Created Date}, "MM/DD/YYYY")
| Note: Close Date and Created Date are placeholders for your date column names. Be sure to use the column names appropriate for your calculation. The TO_DATE() function's format for converting a date field depends on how the date fields are formatted in your input file or SmartConnector™. In this example, the format is "MM/DD/YYYY." If your date is already in ISO format (YYYY-MM-DD), the TO_DATE() function is unnecessary. |
7] Click Save at the bottom of the page.