Copy link to clipboard
Copied
Hi- I have been through many posts about this subject and am unable to find exactly what I am looking for. I do not know java.
I have a very simple timesheet- IN, OUT, TOTAL (for the day), copy attached.
I tried using the below script- it does not calculate properly and when the IN/OUT fields are empty the TOTAL HOURS column contains 0.NAN
var start = this.getField("TIME1").value;
var startArr = start.split(":") ;
var finish = this.getField("TIME2").value;
var finishArr = finish.split(":");
var hourDiff = Math.abs(finishArr[0] - startArr[0]);
var MinDiff = Math.floor(((finishArr[1] - startArr[1]) / 60)*100); if (MinDiff<0) {hourDiff--; minDiff*=-1;}
if (MinDiff.toString().length == 1)
MinDiff = '0' + MinDiff;
var output = hourDiff + "." + MinDiff;
event.value = output;
The 2nd part to this is calculating the TOTAL HOURS column for the WEEKLY TOTAL.
I truly appreciate your time and effort- with each solution I learn.
Thank you.
Copy link to clipboard
Copied
There are no fields in this file.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
There's some kind of corruption in the field. Remove the script, close the Properties dialog and then re-apply it and it should work.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
This calculation assumes a 24-hour notation. Replace "4:00" with "16:00" and it will work just fine.
Copy link to clipboard
Copied
However, you do have an issue with the fields' calculation order. The weekly total field needs to be moved down the list so that it calculates after the daily totals.
Copy link to clipboard
Copied
Thank you.
I fixed the 24 hour format, however I have no idea which field calculation order I need to move.
When the form is blank, why is there 0.NaN in the TOTAL HOURS field?
Copy link to clipboard
Copied
- In Prepare Forms mode go to More - Set Fields Calculation Order.
- It doesn't do that in the new file. This was the bug in the old version. Re-applying the code should have solved it.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
The fields are not empty. They have a single space in them. If you clear them entirely this weird message will disappear. It's also possible to handle this situation in the code, but it will make it more complex.
Setting a minimum of 4 hours can be done by adding the following line:
if (hourDiff<4) {hourDiff = 4; minDiff = 0;}
if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
Copy link to clipboard
Copied
ok- I will try that.
I assume the 4 HR MIN code is to be added to the existing code? If so, is there a particular place I need to insert it?
Copy link to clipboard
Copied
Yes, before the second line, which I copied from the original code.
Copy link to clipboard
Copied
Like this?
var start = this.getField("TIME1").valueAsString;
if (hourDiff<4) {hourDiff = 4; minDiff = 0;}
if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
var finish = this.getField("TIME2").valueAsString;
if (start=="" || finish=="") event.value = "";
else {
var startArr = start.split(":") ;
if (hourDiff<4) {hourDiff = 4; minDiff = 0;}
if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
var finishArr = finish.split(":") ;
var hourDiff = finishArr[0] - startArr[0];
if (hourDiff<0) hourDiff+=12;
var minDiff = Math.floor(((finishArr[1] - startArr[1]) / 60)*100);
if (minDiff<0) {hourDiff--; minDiff += 100;}
if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
event.value = hourDiff + "." + minDiff;
}
Copy link to clipboard
Copied
No, like this:
var start = this.getField("IN1").valueAsString;
var finish = this.getField("OUT1").valueAsString;
if (start=="" || finish=="") event.value = "";
else {
var startArr = start.split(":") ;
var finishArr = finish.split(":") ;
var hourDiff = finishArr[0] - startArr[0];
var minDiff = Math.floor(((finishArr[1] - startArr[1]) / 60)*100);
if (isNaN(hourDiff) || isNaN(minDiff)) event.value = "";
else {
if (minDiff<0) {hourDiff--; minDiff += 100;}
if (hourDiff<0) hourDiff+=12;
if (hourDiff<4) {hourDiff = 4; minDiff = 0;}
if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
event.value = hourDiff + "." + minDiff;
}
}
I also added code to fix the other issue with the space.
Copy link to clipboard
Copied
Thank you. Worked perfectly.
I am grateful for your time.