Copy link to clipboard
Copied
Hello. I am trying to build script for auto-populating dates on a pay period planning tool. The goal is for the user to enter the start of the pay period on the first start date column and then to have the rest of the dates autopopulate every two weeks through the remainder of the pay periods. Below is a snapshot of what it looks like and the first two periods manually filled out. In this case the first pay period started 6/19 through 7/2, the next starts 7/3-7/16, the next would be 7/17-7/30 and so own. Anyone able to help with the script on this?
Copy link to clipboard
Copied
So you want the user to enter the Start date and have the End date automatically calculated as 14 days after it, basically?
Copy link to clipboard
Copied
Yes, and then on the next start date it needs to add 1 day, and then go out another 14 days and so on. Like the example image above. 6/19 + 14 days ends 7/2. +1 day for the next start date to begin 7/3 and then another 14days. Hope that makes sense. The point is to make it so the dates can adjust for two week pay periods over the course of a year but start based on whatever the first start of the pay period is entered, in the example, that is 6/19.
Copy link to clipboard
Copied
What are the names of the fields involved?
Copy link to clipboard
Copied
There are 26 rows, 1 for each pay period. To keep it simple I labled them start1 through start26 for the start date column and end1 through end26 for the end date column. One other challenge just came up. My boss asked if I can account for leap year so we dont have to update the coding on those years. That one threw me even further on this task.
Copy link to clipboard
Copied
That's the beauty of the Date object. It takes care of all of that stuff for you, if you use it correctly.
You can use this code as the custom validation script of one of the start fields to populate its matching end field:
var dateFormat = "mm/dd/yyyy";
var endDateField = this.getField(event.target.name.replace("start", "end"));
if (event.value) {
var d = util.scand(dateFormat, event.value);
d.setDate(d.getDate()+14);
endDateField.value = util.printd(dateFormat, d);
} else endDateField.value = "";
To populate all the other fields after it would require a more complex solution, where you would use a loop to populate both the start and end dates in all subsequent rows.
Copy link to clipboard
Copied
I was trying lots of different approachs and was nowhere close. I really appreciate this. I will try this and look into how to loop it. Thanks for the help!