Summary:
I am the Web TimeSheet administrator for my Company. My Company follows semi-monthly timesheet period for all the employees. The first timesheet for a given month is normally approved within the same month and the second timesheet for the month may or may not be approved in the same month. If it is not approved in the same month, I would like to run a report which shows for all the users, timesheet hours approved on time (that is, timesheet hours approved within the same month) and timesheet hours not approved on time (timesheet hours approved in the following month). I have created a report based on TimeSheet Hours Details template and have incorporated 2 custom columns to achieve this.

Please find the formula for the custom columns below:

Approved on time:
If((Month(ApprovalDateTime)=Month(Timesheetperiodenddate),Total Hours,0))

Not approved on time:
If((Month(ApprovalDateTime)!=Month(Timesheetperiodenddate),Total Hours,0))

Note:
The report works fine in that it shows timesheet hours approved on time and not approved on time correctly. However, if there are more than one timesheet approvers, the timesheet hours are duplicated. Please find attached the screenshot of the report for a user.

Resolution:
If there are more than one timesheet approvers and each of them approve the timesheet at different dates and time, the approval date/time column in the report will have multiple
values. Since the custom column uses the approval date/time field, the “Approved on time” and “Not approved on time” fields will show the timesheet hours multiple times.

There is no perfect solution to the issue. However, to work around the issue, please perform the following steps:

  • Create a timesheet level UDF and name it accordingly. For example, you can name it “Approval”. This is date type UDF. There is no default value attached to it and it is not a required field. It is enabled and not hidden.
  • There can be different scenarios like a user entering time against multiple projects in the timesheet period and his timesheet going to multiple project leaders for approval, or
  • a user entering time against one project and his timesheet going to more than one approvers in an approval sequence.
  • In the first scenario described above, one of the approvers will open the timesheet prior to approving it, click the Edit button, and input the date of approval in the Approval UDF by clicking the Calendar icon next to it and save the timesheet before approving it. In the second scenario, the final approver of the timesheet will perform the step described above. This means that the Approval field has the date of approval.
  • A report based on TimeSheet Status Details should be created and the Approval UDF created in Step 1 should be enabled in the report.
  • Edit the custom columns in the following manner.
           Approved on time
           If((Month(Approval)=Month(Timesheetperiodenddate),Total Hours,0))

            Not approved on time
            If((Month(Approval)!=Month(Timesheetperiodenddate),Total Hours,0))

Run the report. The Approval field will display the date of approval and both Approved   on time and Not approved on time fields will display the timesheet hours approved on time and not approved on time respectively only once.