Reporting 2.0 Calculated Field
Show if a training was completed early, on time, or late
Calculated fields allow you to create additional columns of data based on “Excel Style” formulas. We had a client in need of a calculated field that showed if a training was completed early, on time, late, or not completed yet. Here is how we created this custom field. If you need help creating calculated fields please reach out!
Instructions
First off, we will create a calculated field to show how many days before the due date a user completed the course.
We will use the “DATEDIFF” function for this, which allows us to calculate the difference between two dates. Here’s the start of our formula:
DATEDIFF()
Next, we need to specify what interval we want to use for our calculation. We are going to use days.
DATEDIFF(day,)
To complete the formula we need to add the two dates for the difference calculation. We want to use the due date [Transcript].[Transcript Due Date] and the registration date [Transcript].[Transcript Completed Date]
DATEDIFF(day,[Transcript].[Transcript Due Date],[Transcript].[Transcript Completed Date])
Once we click the validate button we can see that our formula is showing a successful validation and we can click save!
This calculated field alone is super useful as now we can see how many days before the due date a training was completed.
Now that we’ve completed the first calculated field, we need to create a second that determines if the training was completed early, on time, or late. We can do this with a nested if statement that references our first calculated field. Here is the formula we will use:
IF([Calculated Fields].[Days from due to complete]>0,"Completed Late",
IF([Calculated Fields].[Days from due to complete]=0,"Completed On Time",
IF([Calculated Fields].[Days from due to complete]<0,"Completed Early",
"Not Complete")))
You can see this formula will use an if statement to check if the “days from due to complete” is greater than, equal to, or less than 0. Based on the check, it will display the determination on when the training was completed.
Once we save this calculated field we can add it to our report and refresh. You can see that now our report is showing how many days from the due date a training was completed and an easy to read determination on if the course was completed early, on time, or late.
We can take this one step further and add some logic to our IF statement to see if the training is overdue and not completed. In this case, we will have our statement show “Delinquent.”
Here’s the extra line we will add:
IF([Calculated Fields].[Days from due]>-1 &&
[Transcript].[Transcript Status Group]!="Completed"," Delinquent",
In this statement, we are checking if the difference between the due date and today is greater than -1. If so, we know the training is over due. The double ampersand symbols mean “AND” allowing us to combine that with another statement. In the second half of the statement we ensure that the transcript status group is not completed.
This leaves us with a statement that checks if the training is overdue, and if it is overdue and not completed, it will provide a delinquent value.
Here is what our full IF statement looks like now:
IF([Calculated Fields].[Days from due to complete]>0,"Completed Late",
IF([Calculated Fields].[Days from due to complete]=0,"Completed On Time",
IF([Calculated Fields].[Days from due to complete]<0,"Completed Early",
IF([Calculated Fields].[Days from due]>-1 &&
[Transcript].[Transcript Status Group]!="Completed"," Delinquent",
"Not Complete"))))
As you can see, this calculated field makes it super easy for stakeholders to see if training was completed on time or not. As we mentioned before, if you ever need any help with your Cornerstone system or need some help with your Cornerstone administration, please reach out and we will be happy to help!
