Need help making timesheet to calculate total hours possible in a pay period | Community
Skip to main content
nowackem
nowackem
Level 5
February 17, 2022
Solved

Need help making timesheet to calculate total hours possible in a pay period

  • February 17, 2022
  • 2 replies
  • 1749 views

Hi all,

This timesheet calculates and populates dates and days depending on the selection in the date picker. As the user enters their time in and time out pluslunch time in and out, the hours calculate accordingly (see form at the link below). There are a couple of things I would appreciate some assistance on as I am as far as I can go.

Any Saturday and Sunday fields should display a grey background "220, 220, 220"

The Required Work Hours in Pay Period (requiredHrs) amount should calculate how many workday hours (1 day = 8 hrs) are possible for the pay period (not including weekend hours). Take February for example, the 2nd pay period should calculate to 72 as it is a shorter month (9x8). Whereas the first period should be 88 (11x8) this is regardless of how many hours the employee actually works this is just the required work hours per period.

Below is the link to the Designer file and I've included the original Excel file as a reference if needed.

https://drive.google.com/drive/folders/1l8pcqcTVBdvNlEkiK5AdMuTHnsMk9I1J?usp=sharing

Thank you

    This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
    Best answer by MHWinter

    Great! Thanks. I was able to get the Reset Form button working. I have provided the script below (I had to change the ppCurrent.getDay()== to 5 and 6 as those are the numbers for Sat and Sun in this JS). I am still doing something wrong as I move stuff around. Please see this all together and show me where to move the code you were talking about. I really appreciate your time.

     

    for(var i=1; i<=16; i++){
    Table1.resolveNode("Row" + i).Day.rawValue = "";
    Table1.resolveNode("Row" + i).Date.rawValue = "";
    Table1.resolveNode("Row" + i).fillColor = "255,255,255";
    }
    var rowNum = 1;
    var nWorkDays = 0;
    for(var i=pp.getDate(); i<=ppEnd.getDate(); i++){
    pp.setDate(i);
    var ppCurrent = new Date(pp.getFullYear(), pp.getMonth(), pp.getDate());
    Table1.resolveNode("Row" + rowNum).Day.rawValue = days[ppCurrent.getDay()];
    Table1.resolveNode("Row" + rowNum).Date.rawValue = (pp.getMonth()+1) + "/" + ppCurrent.getDate() + "/" + pp.getFullYear();
    rowNum++;
    if (ppCurrent.getDay()==5 || ppCurrent.getDay()==6){ // Weekend day (Sat or Sun)
    Table1.resolveNode("Row" + rowNum).fillColor = "220,220,220"; // set background
    }else{ // Work day (Mon-Fri)
    nWorkDays++;
    }
    }
    Table1.Row18.requiredHrs.rawValue = nWorkDays*8; // sets the required hours (8 hours per work day)
    }


    actually, ppCurrent.getDay()== to 5 and 6 correspond to Friday and Saturday, but because I had the script for coloring the background after the line incrementing row number (rowNum++), if was coloring the row after the one I intended (therefore Sunday Monday (or an empty row if the last day of the PP was a Sat or Sun.

     

    Here is the script that should work:

    //loop through pp
    //clears Day and Date columns to prevent extra rows is user changes month
    for(var i=1; i<=16; i++){
    Table1.resolveNode("Row" + i).Day.rawValue = "";
    Table1.resolveNode("Row" + i).Date.rawValue = "";
    Table1.resolveNode("Row" + i).fillColor = "255,255,255"; // removes background color
    }

    var rowNum = 1;
    var nWorkDays = 0;
    for(var i=pp.getDate(); i<=ppEnd.getDate(); i++){
    pp.setDate(i);
    var ppCurrent = new Date(pp.getFullYear(), pp.getMonth(), pp.getDate());
    Table1.resolveNode("Row" + rowNum).Day.rawValue = days[ppCurrent.getDay()];
    Table1.resolveNode("Row" + rowNum).Date.rawValue = (pp.getMonth()+1) + "/" + ppCurrent.getDate() + "/" + pp.getFullYear();
    // Color backgrounf if Saturday or Sunday
    if (ppCurrent.getDay()==0 || ppCurrent.getDay()==6){
    Table1.resolveNode("Row" + rowNum).fillColor = "220,220,220";
    }else{
    nWorkDays++
    }
    rowNum++;
    }
    Table1.Row18.requiredHrs.rawValue = nWorkDays*8;
    }

    2 replies

    MHWinter
    MHWinter
    Level 4
    February 18, 2022

    regarding the background for Saturdays and Sundays, you can add the following script to the final For loop in 

    form1.page1.Page1.employeeScheduleInfo.PayPerDate::exit

    // Color backgrounf if Saturday or Sunday
    if (ppCurrent.getDay()==0 || ppCurrent.getDay()==6){
    Table1.resolveNode("Row" + rowNum).fillColor = "220,220,220";
    }

      MHWinter
      MHWinter
      Level 4
      February 18, 2022

      and for the required hours, if I understand correctly, it should count 8 hours for each day Mon-Fri.

      If so, the following code addresses both the background on weekends and the required hours calculation.

       

      var rowNum = 1;
      var nWorkDays = 0;
      for(var i=pp.getDate(); i<=ppEnd.getDate(); i++){
      pp.setDate(i);
      var ppCurrent = new Date(pp.getFullYear(), pp.getMonth(), pp.getDate());
      Table1.resolveNode("Row" + rowNum).Day.rawValue = days[ppCurrent.getDay()];
      Table1.resolveNode("Row" + rowNum).Date.rawValue = (pp.getMonth()+1) + "/" + ppCurrent.getDate() + "/" + pp.getFullYear();
      rowNum++;
      if (ppCurrent.getDay()==0 || ppCurrent.getDay()==6){ // Weekend day (Sat or Sun)
      Table1.resolveNode("Row" + rowNum).fillColor = "220,220,220"; // set background
      }else{ // Work day (Mon-Fri)
      nWorkDays++; 
      }
      }
      Table1.Row18.requiredHrs.rawValue = nWorkDays*8; // sets the required hours (8 hours per work day)

        nowackem
        nowackemAuthor
        Level 5
        February 18, 2022

        This is awesome! Thank you. The only thing left is to clear out the grey background if the user chooses a date then chooses another date then another. The grey background stays and does not clear out the user changes the date and/or the user clicks reset form. 

          MHWinter
          MHWinter
          Level 4
          February 18, 2022

          just add the blue line below to the loop that clears the fields before assigning the dates and days

           

          //loop through pp
          //clears Day and Date columns to prevent extra rows is user changes month
          for(var i=1; i<=16; i++){
          Table1.resolveNode("Row" + i).Day.rawValue = "";
          Table1.resolveNode("Row" + i).Date.rawValue = "";
          Table1.resolveNode("Row" + i).fillColor = "255,255,255";
          }

            Terms & ConditionsAccessibility statement

            Loading footer...