Copy link to clipboard
Copied
Hi
Looking for some help.
I found and copied a script to calculate the time spent on a task from 'on' to 'off'. I need this total on each row but I can't figure out why it is causing errors with certain time periods.
I have adjusted the script for each cell, Time 3.0/3.1/3.2 etc
It doesn't matter which row I enter the times in question they always calculate wrong.
Any ideas what I am doing wrong?
Angie
TIA
var start = this.getField("Time 3.0").value;
var startArr = start.split(":") ;
var finish = this.getField("Time 4.0").value;
var finishArr = finish.split(":");
var hourDiff = Math.abs(finishArr[0] - startArr[0]);
var minDiff = Math.floor((Math.abs(finishArr[1] - startArr[1]) / 60)*100);
if (minDiff.toString().lenght == 1)
minDiff = '0' + MinDiff;
var output = hourDiff + "." + minDiff;
event.value = output;
There are errors in the reply currently marked as correct, too. For example, if one of the fields is empty you'll get weird results. Also, it has a typo (MinDiff instead of minDiff) which will cause an error. I've re-written the code to handle these issues and also added the feature that it can calculate a time difference across midnight.
Use the following:
var start = this.getField("Time 3.1").valueAsString;
var finish = this.getField("Time 4.1").valueAsString;
if (start=="" || finish=="")
...
Copy link to clipboard
Copied
Cahnge lenght to length...
Copy link to clipboard
Copied
Beside that, there are other issues with this code. Where did you find it? People keep using it, even though it's wrong.
I recently posted the correct code on the forum, but it's so annoying to search it...
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Thank you for the quick reply. I got the script off this site. I will try to find it again & post the link.
It's closer but I am still missing something
This is where I am at now
var start = this.getField("Time 3.1") .value;
var startArr = start.split(":") ;
var finish = this.getField("Time 4.1") .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;}
var output = hourDiff + "." + minDiff;
event.value = output;
Thanks
Angie
Copy link to clipboard
Copied
This script is also incorrect.
Use this:
var start = this.getField("Time 3.1") .value;
var startArr = start.split(":") ;
var finish = this.getField("Time 4.1") .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 += 100;}
if (minDiff.toString().length == 1) minDiff = '0' + MinDiff;
event.value = hourDiff + "." + minDiff;
Copy link to clipboard
Copied
yay, thank you so much. Really makes my life easier 🙂
Last 2 question.
1. It seems to have trouble recognizing midnight hours as an end time 24:00 & 00:00?
2. Is there any addition to the above script that would account for the transition into the next day, past midnight? If my guys work 21:00 - 02:00 that I get 5 rather than the error.
Copy link to clipboard
Copied
What is about daylight saving time?
Copy link to clipboard
Copied
No, it works perfectly for my day shift guys but not my night shifts as they work from 6pm through to 6am
Thank you for your help with this
Copy link to clipboard
Copied
2. This is possible, but it brings up a bunch of other issues. What is the maximum amount of time between two hours? Can it be more than 24 hours? If so, how can the script know if the time between 21:00 and 02:00 is 5, or 29? etc.
Copy link to clipboard
Copied
My guys do 12-14hour shifts so total is never more. Does this help?
Copy link to clipboard
Copied
Copy link to clipboard
Copied
There are errors in the reply currently marked as correct, too. For example, if one of the fields is empty you'll get weird results. Also, it has a typo (MinDiff instead of minDiff) which will cause an error. I've re-written the code to handle these issues and also added the feature that it can calculate a time difference across midnight.
Use the following:
var start = this.getField("Time 3.1").valueAsString;
var finish = this.getField("Time 4.1").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 (minDiff<0) {hourDiff--; minDiff += 100;}
if (hourDiff<0) hourDiff+=24;
if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
event.value = hourDiff + "." + minDiff;
}
Copy link to clipboard
Copied
wow, this is awesome... thank you so much. This is now doing everything I asked for.
Your time & effort is very much appreciated 🙂
Copy link to clipboard
Copied
can you include the pdf with the script??
Copy link to clipboard
Copied
Any idea of why I'm getting NaN at the end of the calculation?
var start = this.getField("Time InRow1").valueAsString;
var finish = this.getField("Time OutRow1").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 (minDiff<0) {hourDiff--; minDiff += 100;}
if (hourDiff<0) hourDiff+=24;
if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
event.value = hourDiff + "." + minDiff;
}
Copy link to clipboard
Copied
Because the values in the two arrays (finishArr and startArr) are strings, not numbers.
Copy link to clipboard
Copied
Thanks! Really new to script. Any tips on how to fix that?
Copy link to clipboard
Copied
Put Number(...) around each one of them when you access it later on. For example:
var hourDiff = Number(finishArr[0]) - Number(startArr[0]);