Skip to main content
C
Crystalin26320144445h
Participant
October 3, 2022
Answered

Calculating Regular Hours and Overtime Hours

  • October 3, 2022
  • 2 replies
  • 4717 views

I am creating a timesheet that requires taking the total amount of hours worked and slitting them up between regular hours worked and overtime hours worked. 

 

Three columns:

(TotalHours) The first column I have the total amount of hours worked in the week. The caluation to add up the hours worked each day is easy and works. 

(RegHours) The second colum would be the total of regular hours worked, which would equal 40 hours or less. 

(OTHours) The thrid column would be the total hours worked over 40 hours. 

 

Hour would I write the code for the Regular and Overtime hour calculations? 

    This topic has been closed for replies.
    Correct answer Nesa Nurani

    For regular hours use this:
    var h1 = Number(this.getField("TotalHours1").value);
    if(h1 > 40)event.value = 40;
    else
    event.value = h1;

    For overtime hours use this:
    var h2 = Number(this.getField("TotalHours1").value);
    var h = 0;
    if(h2 > 40)h = h2-40;
    if(h2 > 40)
    event.value = h;
    else
    event.value = "";

    2 replies

    Nesa Nurani
    Community Expert
    Nesa NuraniCommunity ExpertCorrect answer
    Community Expert
    October 4, 2022

    For regular hours use this:
    var h1 = Number(this.getField("TotalHours1").value);
    if(h1 > 40)event.value = 40;
    else
    event.value = h1;

    For overtime hours use this:
    var h2 = Number(this.getField("TotalHours1").value);
    var h = 0;
    if(h2 > 40)h = h2-40;
    if(h2 > 40)
    event.value = h;
    else
    event.value = "";

      C
      CJ54235
      Participating Frequently
      October 5, 2023

      Hi Nesa,

       

      Your code is almost exactly what I need. I am trying to modify it to calculate the overtime for week 1 and/or week 2 and then total the hours in "OvertimeTotals" field. Anything over 40 in a week is overtime. I have week 1 hours (HoursTotal_1) and week 2 hours (HoursTotal_2) as fields that are sum totals of daily work time the user inputs. If you need those calculations let me know.

       

      In "RegTotals" custom calculation:

      //regular hours not to exceed 80

      var h1 = Number(this.getField("HoursTotal_1").value+this.getField("HoursTotal_2").value);
      if(h1 > 80)event.value = 80;
      else
      event.value = h1;

       

      In "OvertimeTotals" custom calculation:

      //calculate overtime for week 1
      var h2 = Number(this.getField("HoursTotal_1").value);
      var h = 0;
      if(h2 > 40)h = h2-40;
      if(h2 > 40)
      var output = h;
      event.value = output;

       

      //calculate overtime for week 2
      var h3 = Number(this.getField("HoursTotal_2").value);
      var h = 0;
      if(h3 > 40)h = h3-40;
      if(h3 > 40)
      var output = h;
      event.value = Number(event.value) + Number(output);

       

      The code is VERY close to working. RegTotals calculates correcty. OvertimeTotals is the glitch.

       

      If there is excess of 40 hours for HoursTotal_1 and HoursTotal_2, the script works.

      HoursTotal_1 = 41.50

      HoursTotal_2 = 42.50

      Overtime calculates correct at 4.00

       

      If there is overtime for HoursTotal_2 but not HoursTotal_1, the script works.

      HoursTotal_1 = 32.50

      HoursTotal_2 = 42.50

      Overtime calculates correct at 2.50

       

      The snag comes when there is overtime for HoursTotal_1 but not HoursTotal_2.

      HoursTotal_1 = 41.50

      HoursTotal_2 = 33.00

      Overtime calculates correct at 3.00

       

      If it matters, there is also a custom format script in Overtime Totals:

      event.value = (event.value != 0)?util.printf("%,0.2f",event.value):"";

       

      I am sure it is an issue with the variable "h" names but I cannot seem to figure it out. Thank you in advance for any help you can give me!

      try67
      Community Expert
      try67Community Expert
      Community Expert
      October 5, 2023

      Apologizes if I did clearly explain what I am looking for. Here is the link to the form: https://tinyurl.com/4y3numve. The timesheet has 2 tables ... Week 1 hours are added up in "HoursTotal_1" and week 2 hours are added in "HoursTotal_2" . The one overtime field (OvertimeTotals) should evaluate each HoursTotal independently and any hours over 40 would be shown. IE - Week 1 has 42.5 hours and Week 2 has 39 hours resulting in 2.5 hours of overtime.

       

      Now that I have used that above scenerio, I have another issue. Using Week 1 @ 42.5 hours and Week 2 @ 39 hours, the regular hours (RegTotals) should be 79 and 80. An employee may have less than 40 hrs in a week so my use "if(h1 > 80)event.value = 80" is wrong also.

       


      One thing at a time. For the Overtime Total field you can use this calculation script:

       

       

      var hours1 = Number(this.getField("HoursTotal_1").valueAsString);
var hours2 = Number(this.getField("HoursTotal_2").valueAsString);
var overtimeTotal = Math.max(hours1-40, 0)+Math.max(hours2-40, 0);
event.value = overtimeTotal;

       

      Edited: Code fixed.

       

      try67
      Community Expert
      try67Community Expert
      Community Expert
      October 4, 2022

      This issue was discussed here many (many) times in the past. Did you try searching the forum for past discussions?

      Terms & ConditionsAccessibility statement
      Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices