Create a Custom Formula Field to Calculate Number of Days Between Two Date Fields

Customers of improveit 360 often want to see the number of days between two important date fields in their business process. Perhaps you want to see how many days on average from the time the job is Sold until it is installed. You may want to know the average lead time for vendor orders, by calculating the number of days from an Order to when materials are Received.

 

In this Article, we will provide three (3) formula examples.

1. Project Age - no IF Logic. The first is a Formula field with Number as the Data Output format. This first formula uses only two date fields to calculate the number of days between those dates.
2. Project Age - includes IF Logic on Status. The second formula takes into account IF the Project is in a Status of "On Hold." If it is, we will use a different combination of Date fields to calculate Project Age. If the Project is not On Hold, we will use our original Project Age formula from example 1. 
3. Projects "On Hold" Count - IF Logic on Status. In our third formula example, we are trying to count the number of Projects that are in a Status of On Hold This allows us to SUM those Projects in a report and see what percentage of our Projects are On Hold. Helpful Hint: IF Logic can be particularly useful if your formula is dividing and the denominator of that equation could possibly be a zero (0). 

Does this seem complicated and overwhelming already?!? Not to worry, improveit 360's staff is happy to build custom Formula Fields and Report Formulas for your business, as one of our Advanced Services offerings. Reach out to a member of our Support team to ask for an Advanced Services estimate today! 

 

Example 1: Project Age - Formula Number Field 

This example counts the number of Days from the Sale's Net On Date to the Project Completed Date. Or, said as an equation, "Project Completed Date minus Sale Net On Date"

Navigate to Setup

Create > Objects > Project

In the Custom Fields and Relationships section, click the "New" button. Choose a Field Type of Formula.

Next Name your field and choose a Data Return Type. In this example, we want to return a Number value and we do not need any Decimal Places. So, we change the Decimal Places setting to 0, and click Next.

 

 

Helpful Hint: Use the Advanced Formula window and click the "Insert Field" button to insure you are selecting the correct Field and inserting the exact API Name.

First we will Insert the Project Completed Date:

Then enter an operator of minus (-) Your formula will look like this at this point: 

i360__Completed_On__c -

Next we need to insert the Net On Date from the Sale. 

In the example of the Net On date field, the Field is not on the same record/object as the Completed Date. When Inserting the Field, you will need to select the related Sale record (Sale >) and then can choose from fields on the Sale object:

Once inserted, your finished formula should look like:

i360__Completed_On__c - i360__Sale__r.i360__Net_Date__c

 

You can click Check Syntax to assure there are no errors in your formula. 

Example 2: Project Age with Hold On condition logic - Formula Number Field 

This example also counts the number of Days from the Sale's Net On Date to the Project Completed Date, however, if the Project is in a Status of "On Hold" then we will instead calculate the Sale's Net On Date to the Project Start Date.  

 

Navigate to Setup

Create > Objects > Project

In the Custom Fields and Relationships section, click the "New" button. Choose a Field Type of Formula.

Next Name your field and choose a Data Return Type. In this example, we want to return a Number value and we do not need any Decimal Places. So, we change the Decimal Places setting to 0, and click Next.

 

 

Helpful Hint: Use the Advanced Formula window and click the "Insert Field" button to insure you are selecting the correct Field and inserting the exact API Name.

 

Type in the Advanced Formula window:  IF (ISPICKVAL ( 

Then click the "Insert Field" button and select the Project Status Field:

Once inserted, type , "On Hold"),

At this point your formula should look like this: 

Next we need to tell the system, IF the Project is On Hold what the Output of the formula should be. In this case it is the number of days from the Net On Date to the Project Start Date. Or, said as an equation, "Project Start Date minus Sale Net On Date" 

Next, click the "Insert Field" button and select the Project Start Date Field:

Then enter an operator of minus (-), and use Insert Field to insert the Sale Net On Date. In the example of the Net On date field, the Field is not on the same record/object as the Completed Date. When Inserting the Field, you will need to select the related Sale record (Sale >) and then can choose from fields on the Sale object:

Enter a Comma after the Net On Date. At this point, your Formula should look like this: 

Next we need to tell the system how to calculate the Project Age IF the Project is NOT in a Status of "On Hold." In this example, we will use the Days from the Sale's Net On Date to the Project Completed Date. Or, said as an equation, "Project Completed Date minus Sale Net On Date"

Next, Insert the Project Completed Date:

Then enter an operator of minus (-) and use Insert Field to insert the Net On Date from the Sale. 

Last type an End Parenthesis ) to close the Formula

Once inserted, your finished formula should look like:

 

IF (ISPICKVAL( i360__Status__c, "On Hold"),
i360__Start_Date__c - i360__Sale__r.i360__Net_Date__c,
i360__Completed_On__c - i360__Sale__r.i360__Net_Date__c)

 

Example 3: On Hold Projects Sum & Report on Percentage of Projects On Hold

Our third example references the Status picklist field on the Project. We use IF logic to say "if the Status of the Project is "On Hold" then return a value of 1, else return a value of zero (0). The second part of this Example shows how to create a Report and Report Formula to show the percentage of Projects On Hold for each of our Project Managers.

 

Navigate to Setup

Create > Objects > Project

In the Custom Fields and Relationships section, click the "New" button. Choose a Field Type of Formula.

Next Name your field (On Hold) and choose a Data Return Type. In this example, we want to return a Number value and we do not need any Decimal Places. So, we change the Decimal Places setting to 0, and click Next.

Helpful Hint: Use the Advanced Formula window and click the "Insert Field" button to insure you are selecting the correct Field and inserting the exact API Name.

In the Formula window, type: IF (( TEXT( 

then click Insert Field and choose the Project Status Field:

At this point, your Formula should look like this:

Next Type onto the end of your formula:  ) = "On Hold"),1,0)

The Finished Formula should look like this:

 

IF ((TEXT( i360__Status__c ) = "On Hold" ), 1 ,0 )

 

Now, we will add this new "On Hold" count field to a Projects report and create a Report Formula to see what percentage of our Projects are currently On Hold. 

Navigate to the Reports Tab

Click the New Report button

Choose a Report Type of Projects:

Change the Show filter to: All projects

Change the Date Field filter to: Project Start Date and choose a Range of "Current CY" (or Current Calendar Year)

Your Report should look like this:

Next we'll drag Fields into the Preview pane. In the Field Quick Find I'll select and drag into my Report:

Status, Project Start Date, Project Completed Date and On Hold

At this point, the Preview Pane of Fields looks like this: 

Next, change the Report Format from Tabular Format to a Summary Format:

You will see an area appear in the Preview Pane that says "Drop a field here to create a grouping."

Use the Field Quick Find to locate the Project Manager Field and drag it into the Grouping row:

Next, in the Preview Pane, Hover over the "On Hold" Field and click the Arrow

Then select to Summarize This Field 

Choose Sum and click Apply 

Helpful Hint: The Preview Pane is NOT a factual representation of all data, it is merely a Preview of what your report will look like. To see Actual Data, always click Run Report. 

At this point our Report Preview looks like this: 

Next we will create a Report Formula to give us the Percentage of Projects that are in a Status of "On Hold". 

Clear the Field Search bar and Double-click on Add Formula:

In this example, we'll Name the Column: On Hold %

Format: Percent 

Click on the Summary Fields menu and choose On Hold, then select Sum:

Then Type or choose the Operator for divide (/)

At this point your formula should look like this:

Next choose the Summary Fields menu, and choose Record Count (typically the very first option in the list). The finished Formula should look like this:

Click Validate to assure there are no errors in the Formula. Then click OK

Now click Run Report

Here I can see the total number of Projects that are On Hold for each Project Manager, and the Percentage of On Hold Projects. These concepts and principles can be used to track any custom fields or metrics specific to your organization. 

 

Does this all seem complicated and overwhelming?!? Not to worry, improveit 360's staff is happy to build custom Formula Fields and Report Formulas for your business, as one of our Advanced Services offerings. Reach out to a member of our Support team to ask for an Advanced Services estimate today!