Another timesheet calculation

[steven34895068sco8]

firstly, i do not knkow coding at all.

i am trying to make a fillable timesheet for my employees. i need the job total section to auto populate, i would like it so my guys didnt have to enter times in 24-hr format.  and even better if they didnt have to use ":" when entering times.  *Sorry they are whiny babies!

i am having trouble with a section for job total which should just be 

end time - start time

minutes need to be calculated as a percentage of an hour

i had this in excel but other people were not so excel friendly so im just trying to convert my excel form into a fillable pdf

in excel i simple had this formula in the column

=(a2-a1)*24

any help would be appreciated! 

just lemme know what other info you may need to resolve this

[try67 · Accepted solution]

Try this code:

var a1 = this.getField("a1").valueAsString;
var a2 = this.getField("a2").valueAsString;
if (a1.length!=4 || a2.length!=4) event.value = 0;
else {
	var d1 = util.scand("mm/dd/yyyy HH:MM", "01/01/2024 " + a1.substring(0,2) + ":" + a1.substring(2,4));
	var d2 = util.scand("mm/dd/yyyy HH:MM", "01/01/2024 " + a2.substring(0,2) + ":" + a2.substring(2,4));
	var diffInMinutes = Math.round((d2.getTime()-d1.getTime())/60000);
	event.value = (diffInMinutes/60).toFixed(2)
}

[Nesa Nurani · Accepted solution]

If you want to calculate in 12h format (H:MM tt) 2:00 pm, 8:00 am...etc use this script (it will calculate all 12 rows and total):

var total = 0;
for (var i=1; i<=12; i++) {
var startField = "START TIMERow" + i;
var endField = "END TIMERow" + i;
var totalField = "TOTAL HOURSRow" + i;
var t1 = this.getField(startField).valueAsString;
var t2 = this.getField(endField).valueAsString;

if (t1 !== "" && t2 !== "") {
var d1 = util.scand("mm/dd/yyyy H:MM tt", "01/01/2024 " + t1);
var d2 = util.scand("mm/dd/yyyy H:MM tt", "01/01/2024 " + t2);

var diffInMinutes = Math.round((d2.getTime() - d1.getTime()) / 60000);
var totalTime = (diffInMinutes / 60).toFixed(2);
this.getField(totalField).value = totalTime;
total += parseFloat(totalTime);} 
else {
this.getField(totalField).value = "";}}

event.value = total.toFixed(2);

Here is your file with script added:

https://drive.google.com/file/d/1MtWj8c7v8uvP2C41voCtBKt9BvRy2U3R/view?usp=sharing 

[try67]

You can use exactly the same formula in the Simple Field Notation section of the Calculate tab of the Properties dialog of a text field in Acrobat, just drop the equals sign (and of course adjust the field names, as needed).

[steven34895068sco8]

I tried that but it didn't convert the minutes into a decimal.  ie 5½ hours showed up as 5.3 instead of 5.5

[try67]

What is the exact way the users are specifying these values?

[steven34895068sco8]

So I have a column that says start time and another that says end time.  They put in their values in standard time format

[try67]

So for half past five they would enter "0530" or "1730", ie. military time?

[steven34895068sco8]

Correct

[try67 · Accepted solution]

Try this code:

var a1 = this.getField("a1").valueAsString;
var a2 = this.getField("a2").valueAsString;
if (a1.length!=4 || a2.length!=4) event.value = 0;
else {
	var d1 = util.scand("mm/dd/yyyy HH:MM", "01/01/2024 " + a1.substring(0,2) + ":" + a1.substring(2,4));
	var d2 = util.scand("mm/dd/yyyy HH:MM", "01/01/2024 " + a2.substring(0,2) + ":" + a2.substring(2,4));
	var diffInMinutes = Math.round((d2.getTime()-d1.getTime())/60000);
	event.value = (diffInMinutes/60).toFixed(2)
}

[steven34895068sco8]

sorry but under what tab do  put this

[steven34895068sco8]

im gonna include a copy of the form so you can see what im looking for

[steven34895068sco8]

i need the end time - start time to display in total hours section

the time to display in decimal format ie 2 hours 15 minutes displaying as 2.25 hours

all times displaying in 12 hour format and input in 12 hour format

 please help

ie employee enters 2pm in either start or end time section as 2:00

[Nesa Nurani · Accepted solution]

If you want to calculate in 12h format (H:MM tt) 2:00 pm, 8:00 am...etc use this script (it will calculate all 12 rows and total):

var total = 0;
for (var i=1; i<=12; i++) {
var startField = "START TIMERow" + i;
var endField = "END TIMERow" + i;
var totalField = "TOTAL HOURSRow" + i;
var t1 = this.getField(startField).valueAsString;
var t2 = this.getField(endField).valueAsString;

if (t1 !== "" && t2 !== "") {
var d1 = util.scand("mm/dd/yyyy H:MM tt", "01/01/2024 " + t1);
var d2 = util.scand("mm/dd/yyyy H:MM tt", "01/01/2024 " + t2);

var diffInMinutes = Math.round((d2.getTime() - d1.getTime()) / 60000);
var totalTime = (diffInMinutes / 60).toFixed(2);
this.getField(totalField).value = totalTime;
total += parseFloat(totalTime);} 
else {
this.getField(totalField).value = "";}}

event.value = total.toFixed(2);

Here is your file with script added:

https://drive.google.com/file/d/1MtWj8c7v8uvP2C41voCtBKt9BvRy2U3R/view?usp=sharing 

[steven34895068sco8 · Accepted solution]

@try67 and @Nesa Nurani, thank you both so much for your time and patience with me on this issue.  it works great.