• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

I need help with a form that calculates hours worked.

Explorer ,
Oct 25, 2022 Oct 25, 2022

Copy link to clipboard

Copied

Hello

I have a form that calucates hour worked. There are two time-in and time-out sections that are added together to get the total hours worked. Everything works fine if the time-in and time-out values are in the same 12-hour period. If a worker clocks in at 8:00am and clocks out at 5:13pm the total hours worked is 9:13. All is well in the world.

 

Here is the problem. If a worker clocks in at say 8:00pm and clocks out at 3:00am the next morning, I get a value of -17:00. Something isn't calcuating correctly when we go from pm to am.

The code I have to calculate time in and time out is:

/*
document level functions:
Scand
AdjustMidnight
DateTime2Min
*/

// get the input fields;
var cStartDate = util.printd("dd-mmm-yyyy", new Date());
var cStartTime = this.getField("StartTime").valueAsString;
var cStartDateFormat = "dd-mmm-yyyy";
var cStartTimeFormat = "h:MM tt";
cStartTime = AdjustMidnight(cStartTime);
var cEndDate = util.printd("dd-mmm-yyyy", new Date());
var cEndTime = this.getField("EndTime").valueAsString;
var cEndDateFormat = "dd-mmm-yyyy";
var cEndTimeFormat = "h:MM tt";
cEndTime = AdjustMidnight(cEndTime);
// compute elapsed time in minutes;
event.value = 0;
// compute elapsed time ony if we have data;
if(cStartDate != "" && cStartTime != "" && cEndDate != "" && cEndTime!= "")
{
	event.value = DateTime2Min(cEndDateFormat + " " + cEndTimeFormat, cEndDate + " " + cEndTime) - DateTime2Min(cStartDateFormat + " " + cStartTimeFormat, cStartDate + " " + cStartTime);
} // end date objects not null;
// final format is done through the custom format option;


Any help would be greatly appreciated. I have attached a copy of the form if anyone wants to see exactly how I have it set up. Make sure you add am or pm after the time value
ex: "8:13am" then press the tab button.

Thank you.

TOPICS
Acrobat SDK and JavaScript

Views

3.9K

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
community guidelines

correct answers 2 Correct answers

Community Expert , Oct 26, 2022 Oct 26, 2022

My bad, only time2 is being used in the calculation. So it's rounding out to -1 minute

Here's the corrected code. 

// Get Start and end time strings
var strTime1 = this.getField("StartTime").valueAsString;
var strTime2 = this.getField("EndTime").valueAsString;;

// add code here to check for valid time values, before proceeding.
if(strTime1 != "" || strTime2 != ""){
	// Set Calculation parameters
	var bSameDay = false;
	var strInFormat = "h:MM tt";
	var strOut = null;
	var bNeg = false;
	var str
...

Votes

Translate

Translate
Community Expert , Nov 02, 2022 Nov 02, 2022

So the way to do this is to write the code so it gives you sensible results.

If no (invalid) time is entered in either slot, then a blank result. 

If only one time is entered, then just show that value. 

 

The easy solution is to use a regular expression to detect and set values that will flow seemlessly throught the code. 

 

Like this:

 

var strTime1 = this.getField("HoursWorked2").value; 
var strTime2 = this.getField("hoursWorked").value; 
var aTime1 = [0,0], aTime2 = [0,0];
var rgTimeFormat =
...

Votes

Translate

Translate
Community Expert ,
Oct 25, 2022 Oct 25, 2022

Copy link to clipboard

Copied

You're calculations are a bit overly complex.  The addition of a date is necessary to anchor the calculation to a single day, but no midnight adjustment is needed.  And the formatting is simple to do using the date object functions. 

 

Here is a simpler script that doesn't require the extra functions. 

Notice that this code uses a "bSameDay" variable. If true, then a negative difference in time is left negative, but if false, it is subtracted from 24 hours.  Also notice that the date used is the zero time for the date object, i.e. midnight Dec 31, 1969 in Grenich England. The where and when computer time began.  This is totally arbitrary. It's only necessary the times are on the same day.  

It is assumed the time formatting and numbers are not in error.  Extra code would be necessary if you want time format checking. Do not format the output field. 

 

// Get Start and end time strings
var strTime1 = this.getField("StartTime").valueAsString;
var strTime2 = this.getField("EndTime").valueAsString;;

// add code here to check for valid time values, before proceeding.

// Calculation parameters
var bSameDay = false;
var strInFormat = "h:MM tt";
var strOut = null;
var bNeg = false;
var strFullFormat = "dd-mm-yyyy " + strInFormat;

// get difference in times in miliseconds. 	
var nDiffMs = util.scand(strFullFormat, "31-12-1969 " + strTime2).getTime() - util.scand(strFullFormat, "31-12-1969 " + strTime2).getTime();
if(nDiffMs < 0){
	if(bSameDay){
		bNeg = true;
		nDiffMs = Math.abs(nDiffMs);
	}else // Shift 24 hours
		nDiffMs += 86400000;
}

// format time differnce. 	
var dtDiff = new Date(nDiffMs);
strOut = util.printf("%02d:%02d", dtDiff.getUTCHours(), dtDiff.getUTCMinutes());
if(bNeg) strOut = "-" + strOut;
event.value = strOut;

 

 

 

  

 

 

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Oct 26, 2022 Oct 26, 2022

Copy link to clipboard

Copied

Thomas

I really appreciate your repsonse and simplified code. I apologize for my novice understanding of the solution. Unfortunatly that didn't work - most likely because I didn't implement it correctly in my form. I replaced the script in the "hours" column with your code (both output boxes) and removed the formatting like you suggested. Both boxes now show "23:59" and nothing caluclates.

Can you help me implement your script with my particular form?

Votes

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
community guidelines
Community Expert ,
Oct 26, 2022 Oct 26, 2022

Copy link to clipboard

Copied

My bad, only time2 is being used in the calculation. So it's rounding out to -1 minute

Here's the corrected code. 

// Get Start and end time strings
var strTime1 = this.getField("StartTime").valueAsString;
var strTime2 = this.getField("EndTime").valueAsString;;

// add code here to check for valid time values, before proceeding.
if(strTime1 != "" || strTime2 != ""){
	// Set Calculation parameters
	var bSameDay = false;
	var strInFormat = "h:MM tt";
	var strOut = null;
	var bNeg = false;
	var strFullFormat = "dd-mm-yyyy " + strInFormat;

	// get difference in times in miliseconds. 	
	var nDiffMs = util.scand(strFullFormat, "31-12-1969 " + strTime2).getTime() - util.scand(strFullFormat, "31-12-1969 " + strTime1).getTime();
	if(nDiffMs < 0){
		if(bSameDay){
			bNeg = true;
			nDiffMs = Math.abs(nDiffMs);
		}else // Shift 24 hours
			nDiffMs += 86400000;
	}

	// format time differnce. 	
	var dtDiff = new Date(nDiffMs + 10);
	strOut = util.printf("%02d:%02d", dtDiff.getUTCHours(), dtDiff.getUTCMinutes());
	if(bNeg) strOut = "-" + strOut;
	event.value = strOut;
}else  
	event.value = "";

 

I've also added protection for blank inputs.   To do this right, code should also be added to test for correctly formatted input. 

 

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Oct 27, 2022 Oct 27, 2022

Copy link to clipboard

Copied

Thom

I think we are very close to a solution here. It works for some inputs. There are still a couple bugs I need help working out. Especially when the clock in time is pm and the clock out time is around 12:00am. Also, the total box towards this bottom left is not outputting in the correct format some of the time. I know you are probably a very busy person and I cannot thank you enough for the help you have provided. Would you mind playing with the form to see what might be causing this? I'm a novice and the type of javascript I write is for traversing the dom of websites. This syntax seems a bit more complex.

Votes

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
community guidelines
Community Expert ,
Oct 27, 2022 Oct 27, 2022

Copy link to clipboard

Copied

So apparently the Acrobat JavaScript scand function reads "12:00am" as "12:00pm". Definately a bug.  So the midnight adjust is necessary.  I'm chigrined. But it is very simple. 

 

Change the first lines to this:

// Setup midnight fix
var rgMidnight = /12(\:\d{2}\s?am)/i;
// Get Start and end time strings
var strTime1 = this.getField("StartTime").valueAsString.replace(rgMidnight,"00$1");
var strTime2 = this.getField("EndTime").valueAsString.replace(rgMidnight,"00$1");

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Oct 28, 2022 Oct 28, 2022

Copy link to clipboard

Copied

Thom

You are the man! That worked. I cannot thank you enough. Only one small bug left I was hoping you could give me the answer to

The TOTAL field at the bottom left gives me the sum of both clock in and out rows. If I add the times and tab over, it outputs correctly. If I click anywhere else in the form or tab one too many times, the output format goes awry.

The code I have in that field changes it to the correct output we need. For instance, instead of  1.50 it's 1:30 for 1 and a half hours.

// convert elapsed minutes into h:MM
event.value = (event.value < 0? "-" : "" ) + util.printf("%,101d:%,102d", Math.floor(Math.abs(event.value) / 60), Math.abs(event.value) % 60);


I'm not sure if it's the code or the way I have the cell set up. If you have time, and want to take a crack at it, it would be beyond appreciated and put an end to over a year long battle with this form!

Votes

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
community guidelines
Community Expert ,
Oct 31, 2022 Oct 31, 2022

Copy link to clipboard

Copied

Have you tried testing your format code in the console window?  Once small piece at a time to see what you get? 

 

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Nov 01, 2022 Nov 01, 2022

Copy link to clipboard

Copied

I'm not that good, unfortunatly. It outputs correctly if you tab away from time fields but changes to an unkown format if you tab again or click in any other area of the form. Then, it won't change back to the correct format unless you re-enter the time and tab completely throught the time fields. I didn't write the code.

Votes

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
community guidelines
Explorer ,
Nov 01, 2022 Nov 01, 2022

Copy link to clipboard

Copied

Update. I enabled the console window, opened it and punched in the numbers. Absolutely nothing showed up in the window.

Votes

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
community guidelines
Community Expert ,
Nov 01, 2022 Nov 01, 2022

Copy link to clipboard

Copied

If you remove all formatting. Then the raw summed result of the time fields is displayed. You'll notice that the sum function is replacing the ":" with a decimal and adding them as if they were real numbers. This is not the same as adding time values so it doesn't work. Which was kind of the point of the exercise. The tabbing around different ways doesn't mean anything. You are probably seeing the change because of calculation ordering. 

 

The solution is to write a custom calculation for adding the time strings. There are a couple of ways to do this. One way to exactly like the method used to get the time difference, i.e., convert the string into a date object and then add the date objects, and reconvert to a time string. But since we're just adding, it's probably easier to divided the time string into Hours and Minutes and then add the hours and minutes separtely.  Like this.

 

ar strTime1 = this.getField("HoursWorked2").value; 
var strTime2 = this.getField("hoursWorked").value; 

var aTime1 = strTime1.split(":");
var aTime2 = strTime2.split(":");

var nHours = Number(aTime1[0]) + Number(aTime2[0]);
var nMinutes = Number(aTime1[1]) + Number(aTime2[1]);

nHours += Math.floor(nMinutes/60);
nMinutes = nMinutes%60;
event.value = util.printf("%d:%d",nHours,nMinutes)

 

If you have much more scripting that needs to be done I would stronly suggest you hire a developer.  You seem like you are over your head. 

 

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

Thom

Thank you for this response. Believe it or not, I can follow the code you are writing. It's the syntax of the javascript that is confusing. Many of the functions I am unfamiliar with. I am used to writing React components. Thank you for being patient with me. Thankfully this TOTAL box is the only and last calculation we need done. If we need a future form, we will be looking to hire.

I removed the custom formatting of the TOTAL field and added your code to the custom calcuation section. It immediatly fills up the total field with random numbers. Nothing changes if I add times in the time-in-out rows. Would you mind taking a look?

Votes

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
community guidelines
Community Expert ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

You will need to share the actual file if you want use to examine it. A screenshot is not good enough.

Votes

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
community guidelines
Community Expert ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

Again, you should be testing this code in the console window so you can see whats going on.  

In your screen shot there are no times filled in. So yeah, the code is working on blank values and producting erroneous results. This is as expected. You will need to include code for filtering out invalid values. 

 

  

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

No errors in the console. The time does calculate correctly if I add times to both in-out rows. A single row still outputs random numbers in bottom total field. I did try to write a conditional statement to print a zero if the first box was empty to elimate the random numbers. I tried it both with a ternary and an if-else statement. No affect, of course, because I'm sure my syntax is off.

var strTime1 = this.getField("HoursWorked2").value; 
var strTime2 = this.getField("hoursWorked").value; 

var aTime1 = strTime1.split(":");
var aTime2 = strTime2.split(":");

var nHours = Number(aTime1[0]) + Number(aTime2[0]);
var nMinutes = Number(aTime1[1]) + Number(aTime2[1]);

if(aTime2 == "") {
    event.value = util.printf("0")
} else {
    nHours += Math.floor(nMinutes/60);
    nMinutes = nMinutes%60;
    event.value = util.printf("%d:%d",nHours,nMinutes)
}




Votes

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
community guidelines
Community Expert ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

So the way to do this is to write the code so it gives you sensible results.

If no (invalid) time is entered in either slot, then a blank result. 

If only one time is entered, then just show that value. 

 

The easy solution is to use a regular expression to detect and set values that will flow seemlessly throught the code. 

 

Like this:

 

var strTime1 = this.getField("HoursWorked2").value; 
var strTime2 = this.getField("hoursWorked").value; 
var aTime1 = [0,0], aTime2 = [0,0];
var rgTimeFormat = /^\d+\:\d{2}$/;

if(rgTimeFormat.test(strTime1))
    aTime1 = strTime1.split(":");

if(rgTimeFormat.test(strTime2))
    aTime2 = strTime1.split(":");

var nHours = Number(aTime1[0]) + Number(aTime2[0]);
var nMinutes = Number(aTime1[1]) + Number(aTime2[1]);

nHours += Math.floor(nMinutes/60);
nMinutes = nMinutes%60;
if(!nHours && !nMinutes)
    event.value = "";
else
    event.value = util.printf("%d:%02d",nHours,nMinutes)

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

Thom
It works! I've struggled with regular expressions and admittedly didn't spend a lot of time trying learn them. I learned quite a bit from you and that is appreciated more than I can express. Thank you for helping me work through this. I clearly have a lot to learn with vanilla javascript. When we need a developer to build something like this in the future, I will be reaching out to you.

Votes

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
community guidelines
Community Expert ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

You are welcome and I'm happy to hire out for any development you might have.  

Here's an article on Regular expressions in Acrobat.

https://acrobatusers.com/tutorials/list_and_combo_in_lc/

 

 

Thom Parker - Software Developer at PDFScripting
Use the Acrobat JavaScript Reference early and often

Votes

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
community guidelines
Explorer ,
Nov 02, 2022 Nov 02, 2022

Copy link to clipboard

Copied

I will be reading that tonight, along with going over the solutions you provided. I have also bookmarked your profile. Our company needs this kind of work from time to time so expect us to contact you.

Have a great rest of your week!

Votes

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
community guidelines
New Here ,
Oct 26, 2023 Oct 26, 2023

Copy link to clipboard

Copied

This code works perffect for me except that it doesn't protect for empty values. For example... if I enter 12:00 in the start time and nothing in the end time, it shows 12:00 on the total. If I put 05:00 in the start time and nothing in the end time it shows 19:00 in total time. Same is true if I put a number in the end time and nothing in the start time. Is there a way to keep the total time blank until both times are filled in? 

Votes

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
community guidelines
New Here ,
Oct 26, 2023 Oct 26, 2023

Copy link to clipboard

Copied

LATEST

Also, none of this works on iOS does it? Any way for this to work on iOS also?

Votes

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
community guidelines