Another timesheet calculation question

[jasonz55860161]

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

[Bernd Alheit]

What error does you get?

[jasonz55860161]

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 · Accepted solution]

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

[jasonz55860161]

Well don't i feel like a fool.

Thank you Bernd, i thought i had to keep the format of HH:MM

Thanks again.

[jasonz55860161]

One, follow up Bernd...

Is it possible, since the numbers entered now have no formatting, that i could enter the number using a "." as the separator rather than the colon ":"?

This will eventually be a form to be used in the field on an iPad, and if we have to enter the ":" every time its a bit of a hassle.

What im getting at, is ideally i'd like to enter 56.24 and not 56:24.

Jay

[try67]

Sure, you can do that, but you'll need to adjust your code accordingly to split using ".", instead of ":".

[jasonz55860161]

That's fantastic!

Thanks try67.

Can i throw a curveball...what if i wanted the option of either "." or ":" is that a posibility?

[try67]

Yes, that's possible too, as long as not both are used. Then you can split by one and then the other, or first convert all periods to colons and then split, for example.

[jasonz55860161]

Thanks, i'll try and tinker around with the scripts.

[jasonz55860161]

Hi try67,

can you help me again?

I have a series of subtotals which i am trying to calculate which are depenent upon the other cells being filled in with times.

Field 1 (a starting time figure) = 54:26

Field 2 (a new time figure) = 12:14

Field 3 (first subtotal) = 66:40 (this works no problems)

Field 4 (another time figure) = currently this is not populated as no time is entered

Field 5 (another subtotal) = nan:nan  

I get the above nan:nan in the field, im guessing since the value in field 4 is not populated.

Can you help with the code to remove the nan:nan appearing?

[Bernd Alheit]

What calculation does you use for Field 5?

[jasonz55860161]

Sorry, i got my description mixed up in my previous question.

The input fields (1, 3, 5,...) have no calculations other than the document level script as described above.

In the subtotal fields (2, 4, 6) i am using the same calculation script as i posted above for the inividual fields, i have just included the additional field in the calc.

[Bernd Alheit]

You can add some console output to the function HHMM2Min to see what happens: 

function HHMM2Min(cTime) {
  console.println("cTime: " + 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
  console.println("nMinutes: " + nMinutes);
return nMinutes;