Copy link to clipboard
Copied
I am creating a fillable form for our employees to request vacation time off. I have pieced together calculations scripts from various forums but can not get the result I want. Say an employee wants to take 01/04/19 off. They would enter a start date/time of 01/04/19 8:00 am and end date/time of 01/04/19 5:00 pm. I want the form to calculate the difference in hours. Now the trick is that if they take more than 4 hours (leave after 12 noon) the deduction needs to deduct an hour from the result, to account for the lunch hour so that employee is only charged 8 hours of vacation rather than 9. Here is the script I have pieced together, please advise:
function Date2Num(cFormat, cString) {
// convert cString with cFormat to number of minutes from Epoch date
// convert to date time object
var oDate = util.scand(cFormat, cString);
// convert date time object to minutes
return Math.floor(oDate.getTime() / (1000 * 60));
} // end Date2Num function
// format for input date & time
var cDateFormat = "mm/dd/yy h:MM tt";
// field names
var cStartField = "Start DateTimeRow1";
var cEndField = "End DateTimeRow1";
// get field values
var cStart = this.getField(cStartField).value;
var cEnd = this.getField(cEndField).value;
// clear the result value
event.value = "";
// compute the difference in minutes if there is data
if(cStart !== "" && cEnd !== "") {
if (cEnd > 12) {
nDiff = (Date2Num(cDateFormat, cEnd)-60) - Date2Num(cDateFormat, cStart);
} else {
nDiff = Date2Num(cDateFormat, cEnd) - Date2Num(cDateFormat, cStart);
}
}
// convert to hours
event.value = nDiff / 60;
Copy link to clipboard
Copied
There are errors in your code. You never define the nDiff variable. Once I do that the result is 9, as expected.
Copy link to clipboard
Copied
Thank you can you please tell me what should be used rather in nDiff? I don't know javascript and like I said before I pieced this together from various other forum posts.
Copy link to clipboard
Copied
After this line:
event.value = "";
Add this:
var nDiff = 0;
Copy link to clipboard
Copied
Thanks, I have made that change and now the result is zero. The result should be 8.
function Date2Num(cFormat, cString) {
// convert cString with cFormat to number of minutes from Epoch date
// convert to date time object
var oDate = util.scand(cFormat, cString);
// convert date time object to minutes
return Math.floor(oDate.getTime() / (1000 * 60));
} // end Date2Num function
// format for input date & time
var cDateFormat = "mm/dd/yy h:MM tt";
// field names
var cStartField = "Start DateTimeRow1";
var cEndField = "End DateTimeRow1";
// get field values
var cStart = this.getField(cStartField).value;
var cEnd = this.getField(cEndField).value;
// clear the result value
event.value = "";
var nDiff=0;
// compute the difference in minutes if there is data
if(cStart !== "" && cEnd !== "") {
if (cEnd > 12) {
nDiff = (Date2Num(cDateFormat, cEnd)-60) - Date2Num(cDateFormat, cStart);
} else {
nDiff = Date2Num(cDateFormat, cEnd) - Date2Num(cDateFormat, cStart);
}
}
// convert to hours
event.value = nDiff / 60;
Copy link to clipboard
Copied
Why 8? There are 9 whole hours between 8 am and 5 pm...
Copy link to clipboard
Copied
Per my initial post
"Now the trick is that if they take more than 4 hours (leave after 12 noon) the deduction needs to deduct an hour from the result, to account for the lunch hour so that employee is only charged 8 hours of vacation rather than 9. "
Copy link to clipboard
Copied
Sorry, I missed that. That will require a much more complex calculation...