Before you begin: There can be difficulty in calculating the days between two date fields in a Smart Connector, especially when those fields were not designated to store the 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 Smart Connectors and using calculations. For more information on these topics, please refer to FAQs: Smart Connectors and FAQs: Calculations.
Instructions:
Create a Custom Calculation
1] Log into TapClicks and on the Data menu, click Calculations.
2] In the top right corner, click the Add New Calculation (i.e., plus sign) icon.
3] On the New Calculation page, enter an appropriate name for the calculation (e.g., Smart Connector Name Date Calculation) in the Name textbox.
4] Select Number from the Data Type dropdown.
5] From their 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")
Notes:
- Close Date and Created Date are placeholders for date column name. Be sure to use the column names appropriate for your calculation.
- The TO_DATE() function's format for converting {Close Date} to a date depends on how the date fields are formatted. Determine this from the input file or Smart Connector. In this case, the format is "MM/DD/YYYY." If the date is already in ISO format (YYYY-MM-DD), using the TO_DATE() function is unnecessary.
7] Click Save at the bottom of the page.