Calculating the difference of Two dates on Smart forms

Calculating the difference of Two Dates on Smart Forms

In some cases you may need to calculate the difference between two date fields on your SmartForm.  For example;

1.  You have a SmartForm that has a date you need an action to happen on the project, for example an action by field;  this tells the reader of the form on this project the date that this action (whatever it may be) is needed. 

2.  You have another date picker of when the action was actually completed.  

You may in this case want to display the difference in the two date fields as a simple subtract calculation expressed in calendar days.  

This is achievable in Cora PPM with SmartForms, however as dates are stored in the database as their full Timestamp descriptor  i.e.: "Y-m-d H:i:s"  then a conversion is needed in order to perform this calculation.  

So you have a date picker that is called "This is Needed By..." on your Smart Form.  For this illustration we will call it "Date 1".  

Let's say then you have another date on the SmartForm that tells the reader when the action was actually done, which you called "Actual Date".  We will call this "Date 2"

Now you want to calculate the variance between the 2.  We will call this "Date Difference".  

To do this, you need to obviously create:

Date 1: A SmartForm field that is DATE type.  In this example this field is configured as ID1928. 

Date 2: A SmartForm field that is DATE type.  In this example this field is configured as ID1929. 

Note: The Date 2 field could also be a DATE FUNCTION field and configured as 'today' in the formula editor if you wanted to show this field as today's date, if you are calculating the difference between the date the user picked for Date field 1 and today.  However in this example we will just assume Date 1 and Date 2 are being entered by the user.

Date Difference: You cannot create this field as a Numeric Function field in the function editor, as the dates are stored in the database using their full Timestamp descriptor ##-##-#### ##:##:##.    

So to get around this you need to add another field for every date field.  In this example we have added ID1931 for Date 1's conversion, and ID1932 for Date 2's conversion

You must create these conversion fields as DATE FUNCTION types. 

Now on the formula editor, link this conversion field to its matching Date 1 or 2 original field.

Once this is done, you can now create the "Date Difference" field. When doing so, make sure to create this field type as NUMERIC FUNCTION FOR DATES 

Then you can in the formula in the formula editor. 

However, instead of just doing a Date 2 - Date 1 formula (of the conversion fields), it is important to include the "N" in the calculation behind each date field.  Therefore. Date 2 Conversion - Date 1 Conversion using the field IDs would be as ID1932 - ID1931.  However you need to insert the formula as ID1932N-ID1931N.  This tells Cora PPM to treat these calculations as numbers. 

Press save, go to your SmartForm with these dates on a project, and run the "Recalc" command on the Project Summary Bar.  Review the results:

The "Date Difference" field is now expressing the difference in calendar days of Date 2 - Date 1 but is using the conversion fields to get there.  You can now hide the 2 conversion fields off the SmartForm that you want to display to the users, however you cannot delete / archive them.