Another timesheet calculation question
Hello Everyone,
like many here, i am a newbie to the world of Javascript and im having a hard time getting this calculation working.
I have a sheet, where a column of fields is entered manually with a time value, say 1:43. At the bottom of the page is a "total" field which i have managed to get to tally all the fields above it, except there is a problem, I cant get hours more than 24, so if the tally is greater than 24 hrs it produces an error. Also, i am unable to enter a time of greater than 23:59 in any feild.
This is a problem , as engine run times are in hours and minutes, and not in days, hours and minutes.
These are the codes ive found to work in a basic way, but any help in getting it to displah hours of greater than 24 would be appreciated.
Jay
// custom calculations script for the total time field
// array of fields to total
var aFields = new Array("Time.0", "Time.1", "Time.2", "Time.3", "Time.4", "Time.5", "Time.6");
// define total accumulated minutes
var nSum = 0;
// variable for time string value of a field
var sTime;
// convert the time fields to minutes and total the minutes
for(i = 0; i < aFields.length; i++) {
// get the time string for the i element field name
sTime = this.getField(aFields[i]).valueAsString;
// convert blank fields to 00:00;
if(sTime == "") sTime = "00:00";
// convert time string to minutes and sum
nSum += HHMM2Min(sTime);
} // end loop through the fields
// format sum of minutes
event.value = Min2HHMM(nSum);
// end of custom calculation field// document level functions
function HHMM2Min(cTime) {
// convert time formatted as HH:Mm to minutes
// spit input time string to an array
var aHHMM = cTime.split(":");
// define total minutes
var nMinutes = 0;
// convert 0 element from hours to minutes
nMinutes = aHHMM[0] * 60;
// add minutes from 1 element of array
nMinutes += Number(aHHMM[1]);
// return the time value as minutes
return nMinutes;
} // end HHMM2Min
function Min2HHMM(nMinutes) {
// convert minutes to HH:MM string
// return formatted time string
return util.printf("%,001.0f:%,202.0f", Math.floor(nMinutes / 60), nMinutes % 60);
} // end Min2HHMM
// end document level functions
