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

[nowackem]

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

[MHWinter]

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]

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]

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]

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";
}

[nowackem]

So close. It does break on 4/16/22. Any thoughts? Also how do I clear the shading in the Reset button? I've tried a couple things but I'm not able to get it going.

[MHWinter]

for the reset button, add the lines in blue:

xfa.host.resetData();
for(var i=1; i<=16; i++){
resolveNode("form1.page1.Page1.Table1.Row" + i).fillColor = "255,255,255";
}

regarding the background, my mistake, move the script above the row++ line.

[MHWinter]

I meant: rowNum++

[nowackem]

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)
}

[MHWinter · Accepted solution]

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;
}

[nowackem]

I was wondering what was up with the days, thank you for the explanation as it now makes total sense. I was so confused for a bit.

With that said, this is now working perfectly! Thank you, Thank you!