Skip to main content
Known Participant
August 22, 2020
Answered

Another timesheet calculation question

  • August 22, 2020
  • 1 reply
  • 1258 views

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

 

This topic has been closed for replies.
Correct answer Bernd Alheit

You can set the format of the fields to "None".

1 reply

Bernd Alheit
Community Expert
Community Expert
August 22, 2020

What error does you get?

Known Participant
August 22, 2020

Hi Bernd,

 

The error message is "the value entered does not match the format of the field"

 

This occurs if i put a value larger than 24hrs in any of the input field, or it happens if the "total" field is trying to calculate a total which is greater than 24hrs.

 

So my guess is that since values of the HH:MM format are not compatible with the numbers im entereing, but i dont know what format or script i need so i can enter a value of 56:24.

 

Does that make sense?

 

Bernd Alheit
Community Expert
Bernd AlheitCommunity ExpertCorrect answer
Community Expert
August 22, 2020

You can set the format of the fields to "None".