Creating a Formula-Based Enterprise Custom Field to Track Rollup Work Hours
I was recently posed with a challenge to create a custom Microsoft Project Online field to calculate the difference between a custom numeric value and Actual Work, specifically the rollup of number of work hours completed on project tasks. Simple enough right? I knew it could be done but the execution of meeting this customer requirement wasn’t straight forward.
The client utilized the work column to forecast their efforts like most MS Project users but they also wanted track committed hours which was the effort their sales team proposed to their clients. They wished to do this because at times work could be adjusted for ramp up, setup time, and other tasks non-billable tasks. What they wanted to capture was the true effort their resources were doing above the sales proposed estimate so they could consider past data to more accurately quote customers in future engagements. With some simple configuration we were able to meet this requirement successfully. How was it done? We’re going to consider that but before we do, let’s talk about their environment.
Creation of the Custom Fields and Testing
Step one to meet the requirement was to create the custom field for Sold Work. From Server Settings, I navigated to Enterprise Custom Fields and Lookup Tables and clicked New. From there I create Sold Hours with the following settings:
From there, I had to create a custom field to calculate the difference between the single value of the Sold Hours field minus the out of the box Actual Work column. Seemed easy enough thus far, so I created the following:
In testing, I had 60.8 hours of actual work in my project and the sold hours were 75.8 so I was expecting a value of 15 in my Remaining Sold Hours field. Project Online gave me a different value. How could I be that off?
After analyzing the relationships between the values I came to understand that Project Online wasn’t comparing apples to apples with my two values.
In my original formula I knew MS Project Online must be calculating Actual Work differently as my newly created Sold Hours column was very straight forward yet I was getting a large negative value (-3572.2).
With a critical eye, I realized MS Project defaulted their Actual Work Rollup field to be in minutes. Aha! From there it was just a matter of adjusting my formula knowing from elementary school I was taught that there are 60 minutes in 1 hour. I first had to convert my Sold Hours to minutes before subtracting the Actual Work Value. Once I got the minutes value of the difference, I had to reconvert back the difference to hours by dividing by 60.
Below is what my new formula looks like:
And here is the end result as displayed on the Project Detail Page. Don’t forget to Save and Publish to trigger the recalculation!
If you need additional assistance with creating a custom Microsoft Project Online field or just want to learn more about SharePoint, contact us by filling out the form below.