Another timesheet calculation question

New Here ,
Aug 22, 2020

Copy link to clipboard

Copied

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

 

Adobe Community Professional
Correct answer by Bernd Alheit | Adobe Community Professional

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

TOPICS
Acrobat SDK and JavaScript, Edit and convert PDFs, General troubleshooting, How to, PDF forms

Views

79

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Another timesheet calculation question

New Here ,
Aug 22, 2020

Copy link to clipboard

Copied

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

 

Adobe Community Professional
Correct answer by Bernd Alheit | Adobe Community Professional

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

TOPICS
Acrobat SDK and JavaScript, Edit and convert PDFs, General troubleshooting, How to, PDF forms

Views

80

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Aug 22, 2020 0
Adobe Community Professional ,
Aug 22, 2020

Copy link to clipboard

Copied

What error does you get?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
New Here ,
Aug 22, 2020

Copy link to clipboard

Copied

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?

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
Adobe Community Professional ,
Aug 22, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
New Here ,
Aug 22, 2020

Copy link to clipboard

Copied

Well don't i feel like a fool.

 

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

 

Thanks again.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
New Here ,
Aug 22, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
Most Valuable Participant ,
Aug 22, 2020

Copy link to clipboard

Copied

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

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
New Here ,
Aug 22, 2020

Copy link to clipboard

Copied

That's fantastic!

 

Thanks try67.

 

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

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
Most Valuable Participant ,
Aug 22, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
New Here ,
Aug 22, 2020

Copy link to clipboard

Copied

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

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 22, 2020 0
New Here ,
Aug 23, 2020

Copy link to clipboard

Copied

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?

Screen Shot 2020-08-24 at 13.21.22.png

 

 

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2020 0
Adobe Community Professional ,
Aug 23, 2020

Copy link to clipboard

Copied

What calculation does you use for Field 5?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2020 0
New Here ,
Aug 23, 2020

Copy link to clipboard

Copied

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.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 23, 2020 0
Adobe Community Professional ,
Aug 24, 2020

Copy link to clipboard

Copied

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;

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Aug 24, 2020 0