3 Replies Latest reply: Mar 7, 2017 2:29 AM by Sunny Talwar RSS

    Calculate Regular and Overtime Hours

    ANDY SALLEE

      Good day Community,

       

      Have a problem that have not been able to solve. Given the below data I would like to calculate the regular time hours & overtime hours for each team member by Region,Week #. and I have just been stumped.

      Appreciate any thoughts as probably making this tougher than should be.

       

      Thanks

       

      Data feed     

      EECodeRegionWeek #DayEarnHours
      0379Eagle117.68
      0379Eagle117.23
      1082Midcon115.03
      1082Midcon115.58
      0379Eagle117.05
      1082Midcon115.13
      0370Eagle117.33
      1082Midcon115
      0370Eagle117.27
      0370Eagle18.87
      1082Midcon20.93
      1082Midcon215.13
      1082Midcon214.85
      0379Eagle214.95
      0379Eagle214.82

       

      Desired result in pivot table

            

      RegionWeekEECodeTotal HoursRegular TimeOvertime
      Eagle    125.2109.7715.43
        1  95.438015.43
          037051.964011.96
          037943.47403.47
        2  29.7729.770.00
          037929.7729.770.00
      Midcon    91.6570.9120.74
        1  60.744020.74
          108260.744020.74
        2  30.9130.910.00
          108230.9130.910.00
      Grand Total  216.85180.6836.17