Skip to main content
Known Participant
Aug 22, 2020 7:57 AM UTC
Answered

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

 

This topic is closed to new replies. Start a new post to keep the conversation going.
Bernd Alheit
Bernd AlheitCorrect answer
Community Expert
Aug 22, 2020 8:51 AM UTC

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

13 replies

Bernd Alheit
Community Expert
Community Expert
Aug 22, 2020 8:36 AM UTC

What error does you get?

Translate
Known Participant
Aug 22, 2020 8:45 AM UTC

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?

 

Translate
Bernd Alheit
Community Expert
Bernd AlheitCommunity ExpertCorrect answer
Community Expert
Aug 22, 2020 8:51 AM UTC

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

Translate