Scenario:
A report should be designed that will show the names of Users who have not met their Weekly Hours Target. This Target is determined based on the Users Location, hence the formula should check for the location and decide on the Weekly Target.
The report should not show the names of any User who has achieved the Target.
The report should also show the related Department Name as well as the Total Hours the user has achieved.

Target:
Weekly Total Hours Target=35 hours if the location is NY, NK or BN
Weekly Total Hours Target=37.5 hours  if the location is DC, NO, CH, LA or BR

Resolution:
Select the Timesheet Detail report because this report has all the required fields.
Do not enable any of the standard columns offered in the report. If we do this, the report will show all Users Name instead of only those who have not met the Target.

  • Click on the Settings button.
  • Click on Add at the end of the Settings to add Custom Formula.
  • Key in the below mentioned Formula for each column. Keep clicking on Add button to create new Custom columns so you can have four columns as shown in the picture.

Custom Column Name: Weekly Hours Target Met
Formula:
If(Location="NY")||(Location="NK")||(Location="BR")&&(TotalHours>=35),"OK", If((Location="DC")||(Location="NO")||(Location="CH")||(Location="LA")||(Location="BR")&&(TotalHours>=37.5), "OK", "Not Met Weekly Hours")

Custom Column Name: User Name
Formula:
If(If(Location="NY")||(Location="NK")||(Location="BR")&&(TotalHours>=35), 1, If((Location="DC")||(Location="NO")||(Location="CH")||(Location="LA")||(Location="BR")&&(TotalHours>=37.5), 1, 0), "", UserName)

Custom Column Name: Department Name
Formula:
If(If(Location="NY")||(Location="NK")||(Location="BR")&&(TotalHours>=35),1, If(Location="DC")||(Location="NO")||(Location="CH")||(Location="LA")||(Location="BR")&&(TotalHours>=37.5), 1, 0), "", DepartName)

Custom Column Name: Total Hours
Formula:
If(If(Location="NY")||(Location="NK")||(Location="BR")&&(TotalHours>=35),1, If(Location="DC")||(Location="NO")||(Location="CH")||(Location="LA")||(Location="BR")&&(TotalHours>=37.5), 1, 0), "", NumberToText(TotalHours))