Using DDIFF() and NOW() in Advanced Analytics Formulas

Calculate a project's completion percentage based on project start/end dates and today using the following:

Days in a Project

Create a formula to calculate number of days in a project: 
 

• MAX(DDIFF([Days in Project End Date],[Days in Project Start Date]))

Days Left in a Project

Next, create a formula to calculate the number of days left in the project based on today and project end date:
 

• MAX(DDIFF([Days in Project End Date],NOW()))

Days Into a Project

To return the days into a project based on today, use project start date instead of end:
 

• MAX(DDIFF(NOW(),[Days in Project Start Date]))

Then, create a formula that combines the above. It may be helpful to save each above formula with a name that is recognizable.

Project Percentage Completed

To show days into a project and the percentage completed based on project start date and today:
 

• MAX(DDIFF(NOW(),[Days in Project Start Date])) / MAX(DDIFF([Days in Project End Date],[Days in Project Start Date]))

Once you have the value per project, adjust the number format to percentage, if desired. Here's a sample:

 

Project Percentage Left to Complete

To show days left in a project and the percentage that is left to complete based on project end date and today:
 

• MAX(DDIFF([Days in Project End Date],NOW())) / MAX(DDIFF([Days in Project End Date],[Days in Project Start Date]))