Attendance by Day & OT Hours calculated columns
Posted: Wed Aug 01, 2018 8:15 am
To create a customized report that will help with Payroll by showing Over Time hours, you can add two Calculated Columns to the Attendance by Day report.
IMPORTANT NOTE: This will only wok with Total Hours which are in 10ths of an hour- it will not work for Duration which is in minutes.
To create this report you will go to Reports from the home ribbon | All Reports | Employee | Time Clock | Attendance | Select Date Range | Execute from the ribbon | Calculated Columns |
First column to create:
Column Name = RegHrs | Column Header =Regular Hours | Data Type = System.Decimal | Format = g2 | Formula = If([TotalHours]>40,40,[TotalHours])
Second Column to create:
Column Name = OTHrs | Column Header = Overtime Hrs | Data Type = System.Decimal | Format = g2 | Formula = If([TotalHours]>40, ([TotalHours]-40),0)
Third Column to create:
Column Name = Duration | Column Header = Duration | Data Type = System.String | Format = (blank) | Formula = Concatenate(If(Len(Floor([TotalHours])) < 2, Concatenate('0', Floor([TotalHours])), Floor([TotalHours])), ':', If(Len(Floor(([TotalHours] - Floor([TotalHours])) * 60)) < 2, Concatenate('0', Floor(([TotalHours] - Floor([TotalHours])) * 60)), Floor(([TotalHours] - Floor([TotalHours])) * 60)))
Be sure to click Add after you create each calculated column.
IMPORTANT NOTE: This will only wok with Total Hours which are in 10ths of an hour- it will not work for Duration which is in minutes.
To create this report you will go to Reports from the home ribbon | All Reports | Employee | Time Clock | Attendance | Select Date Range | Execute from the ribbon | Calculated Columns |
First column to create:
Column Name = RegHrs | Column Header =Regular Hours | Data Type = System.Decimal | Format = g2 | Formula = If([TotalHours]>40,40,[TotalHours])
Second Column to create:
Column Name = OTHrs | Column Header = Overtime Hrs | Data Type = System.Decimal | Format = g2 | Formula = If([TotalHours]>40, ([TotalHours]-40),0)
Third Column to create:
Column Name = Duration | Column Header = Duration | Data Type = System.String | Format = (blank) | Formula = Concatenate(If(Len(Floor([TotalHours])) < 2, Concatenate('0', Floor([TotalHours])), Floor([TotalHours])), ':', If(Len(Floor(([TotalHours] - Floor([TotalHours])) * 60)) < 2, Concatenate('0', Floor(([TotalHours] - Floor([TotalHours])) * 60)), Floor(([TotalHours] - Floor([TotalHours])) * 60)))
Be sure to click Add after you create each calculated column.