Hi Everyone,
We assign billable hour targets as a percentage of full-time hours to each of our consultants, who log their time in timesheets (i.e., 80% of full time (working days in a month * 8h)).
I'm trying to create a report that shows each user's billable target for the current month being displayed in the report (referenced through $ReportedBy?). I've created a Percentage custom field in User called Billable Percentage ($C_BillablePercentage) and populated it with the target percentages for each user. I've also created a custom field Reference to Object->User in Timesheets called Billable Percentage ($C_BillablePercentageRef).
Now I'm trying to create another custom formula field in Timesheet that calculates the number of billable hours for each consultant for that month and displays it either in a chart or a table. This field is Month Billable Target ($C_MonthBillableTarget). The formula I'm using is:
Hours(((DateSubtract(Date(Year($ReportedDate),(Month($ReportedDate)+1),1),Date(Year($ReportedDate),(Month($ReportedDate)),1),TRUE)/DAYS(1))*8)*ToNumber(ToString($C_BillablePercentageRef)))
The result is coming up empty, so I know I'm missing something fundamental here. Any suggestions?
Thanks in advance!
Please sign in to leave a comment.
Hi Ari,
You mentioned that $C_BillablePercentageRef field is a reference to User entity. This means that ToNumber(ToString($C_BillablePercentageRef)) is not a valid number. My suggestion is to make the $C_BillablePercentageRef field a numeric field and populate it with a value at the point of timesheet creation with $ReportedBy.C_BillablePercentage (by using a workflow rule). Then your formula should work.
I hope this helps.
Roland