Copy link to clipboard
Copied
I found a JavaScript for time difference calculations and am trying to understand why I am getting a negative value when entering a time into field 1 ("Start"). For instance, I enter 10:00PM into the "Start" field and have not entered a time into the second field "LunchIn", though I am getting a negative value calculated by the script (-22). The problem is corrected when time is entered into the second field, though I am wondering if there is a way to prevent the script from calculating the time difference when only one field has input.
function sTime2Minutes(cFormat, sValue) {
// convert date time string number of minutes since epoch date;
// test for successful conversion
var oDate = util.scand(cFormat, sValue);
if(oDate == "") app.alert("Error in time conversion of " + sValue + " using format " + cForamt, 1, 0);
// return number of minutes;
return Number( util.printf("%,100.0f", oDate.getTime() / (1000 * 60) ) );
} // end sTime2Minutes function;
function Min2Time(nMinutes) {
// format number of minutes to hours ":" minutes;
return util.printf("%,001.0f:%,002.0f", Math.floor(nMinutes / 60), (nMinutes % 60));
} // end Min2Time function;
var cStart = "Start" // start field name;
var cEnd = "LunchIn"; // end field name;
// the time format string
var cTimeFormat = "h:mm tt";
// convert end time to minutes
var nEnd = sTime2Minutes("dd-mmm-yyyy " + cTimeFormat, "01-Jan-1970 " + this.getField(cEnd).valueAsString);
// get start time minutes
var nStart = sTime2Minutes("dd-mmm-yyyy " + cTimeFormat, "01-Jan-1970 " + this.getField(cStart).valueAsString);
// compute difference in minutes
var nDiff = nEnd - nStart;
// format the results for display;
// whole hours and remainder of minutes when divided by 60;
// event.value = Min2Time(nDiff);
// or
// format display to hours.decimal hours
event.value = util.printf("%,001.2f", nDiff / 60);
Change the field calculation order.
The problem with this form isn't the calculations themselves, it's the calculation order. The fields are completely out of sequence.
You'll fine the calculation ordering tool on the "More..." dropdown in the "Prepare Forms" panel
Copy link to clipboard
Copied
I am also getting a negative value when calculating a grand total with the basic sum function even though there are no negative totals on the sheet.
Copy link to clipboard
Copied
How does you calculate the totals?
Copy link to clipboard
Copied
You'll need to share the entire file or post the code of all the internal functions used in your code (sTime2Minutes, Min2Time)...
Copy link to clipboard
Copied
Thank you. Any idea how to attach a PDF here?
Copy link to clipboard
Copied
You can attach it to the original message using the tiny paperclip icon at the bottom when you edit it, or upload it to a file-sharing website (like Dropbox, Google Drive, Adobe Cloud, etc.), generate a share link and then post it here.
Copy link to clipboard
Copied
Thank you for your help. I wasn't able to edit the post.
Here is a link: https://drive.google.com/file/d/1eMm2HkF-EbroSXz-Lccet25nm33NFCK2/view?usp=sharing
Copy link to clipboard
Copied
Change the field calculation order.
Copy link to clipboard
Copied
The problem with this form isn't the calculations themselves, it's the calculation order. The fields are completely out of sequence.
You'll fine the calculation ordering tool on the "More..." dropdown in the "Prepare Forms" panel
Copy link to clipboard
Copied
Ah ha! I think that did the trick. Thanks a bunch 😄
Copy link to clipboard
Copied
Hello, I am getting negative numbers with this calculation.
I.e. start 22:45 End 03:15 result is -20:30 instead 04:30 I am trying to do flight time calculation departure time is 22:45 arrival time 03:15 next day, I need total hours flown
var hrsStart = parseInt(this.getField("time2_1").value.split(":")[0]); var minStart = parseInt(this.getField("time2_1").value.split(":")[1]); var hrsEnd = parseInt(this.getField("time4_1").value.split(":")[0]); var minEnd = parseInt(this.getField("time4_1").value.split(":")[1]); if (minStart > minEnd) { var minRez = 60 + minEnd - minStart; var hrsRez = hrsEnd - 1 - hrsStart; } else { var minRez = minEnd - minStart; var hrsRez = hrsEnd - hrsStart; } this.getField("flight1").value = hrsRez + ":" + minRez; if(flight1 < 0) {// Shift 24 hours flight1 += 24; }
Copy link to clipboard
Copied
Check the Javascript console for errors.
Copy link to clipboard
Copied
First, never post code like this. Always format it so we can read it.
Now, as try67 pointed out, the last bit of code is definatley an error. There is no variable named "flight1".
And this will cause an error that is reported in the console.
This code is a bit long winded, and to be "correct" it needs to include actual dates. With the dates you could simplify the calculation using the date object as the basis of the calc.
However, since the assumtion is that flights are less than 24 hours and a starting time greater than the ending time represents a midnight crossing, this calculation is simpler and more compact.
// These functions only need to be defined once. Place them in a document script if
// there are more fields that perform similar time calculations.
function TimeStr2Num(strTm)
{
var aParts = strTm.value.split(":");
return Number(aParts[0]) + aParts[1]/60;
}
function TimeNum2Str(nTm)
{
var aParts = nTm.toString().split(".");
return util.printf("%02d:%02d",aParts[0],Number("."+aParts[1])*60);
}
// This is the main calculation code.
nDiff = TimeStr2Num(this.getField("time4_1")) - TimeStr2Num(this.getField("time2_1"));
if(nDiff < 0)
nDiff += 24;
strTime = TimeNum2Str(nDiff);
If the 24hour assumption is true for all calculations the 24 hour correction could be rolled into the "TimeNum2Str" function.
Copy link to clipboard
Copied
My apologies for the ugly code I sent, I am new.
Can someone take a look at the PDF form, it will make more sense.
if you see flight sector 1 Out - In equals 4:10 and Off - On equals -20:10 ( needs to show 4:10 )
Regards
https://www.dropbox.com/s/omenjl247x4xvkc/NXAL%20Aircraft%20Demo_Ferry%20Record.pdf?dl=0
Copy link to clipboard
Copied
Again, the code is a mess. I would suggest cleaning it up. I would also suggest using the time functions I provide in my last post.
If you would simply like someone to fix the form, I would be happy to do this for a fee. Please contact me through this forum.