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

TIMESHEET CALCULATION-

Explorer ,
Apr 20, 2022 Apr 20, 2022

Copy link to clipboard

Copied

Hi- I have been through many posts about this subject and am unable to find exactly what I am looking for.  I do not know java.

I have a very simple timesheet- IN, OUT, TOTAL (for the day), copy attached.

I tried using the below script- it does not calculate properly and when the IN/OUT fields are empty the TOTAL HOURS column contains 0.NAN

 

var start = this.getField("TIME1").value;
var startArr = start.split(":") ;
var finish = this.getField("TIME2").value;
var finishArr = finish.split(":");
var hourDiff = Math.abs(finishArr[0] - startArr[0]);
var MinDiff = Math.floor(((finishArr[1] - startArr[1]) / 60)*100); if (MinDiff<0) {hourDiff--; minDiff*=-1;}
if (MinDiff.toString().length == 1)
MinDiff = '0' + MinDiff;
var output = hourDiff + "." + MinDiff;

event.value = output;

 

The 2nd part to this is calculating the TOTAL HOURS column for the WEEKLY TOTAL. 

I truly appreciate your time and effort- with each solution I learn.

Thank you.

 

TOPICS
PDF forms

Views

1.8K

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 ,
Apr 20, 2022 Apr 20, 2022

Copy link to clipboard

Copied

There are no fields in this file.

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 ,
Apr 20, 2022 Apr 20, 2022

Copy link to clipboard

Copied

Sorry- I just included a generic form without the company info, but I guess it doesn't matter.  I have attached the file I've been working with.

 

Thank you so much.

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 ,
Apr 20, 2022 Apr 20, 2022

Copy link to clipboard

Copied

There's some kind of corruption in the field. Remove the script, close the Properties dialog and then re-apply it and it should work.

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

Thank you.

I am still getting the same result.

 

 

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

 This calculation assumes a 24-hour notation. Replace "4:00" with "16:00" and it will work just fine.

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

However, you do have an issue with the fields' calculation order. The weekly total field needs to be moved down the list so that it calculates after the daily totals.

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

Thank you.

 

I fixed the 24 hour format, however I have no idea which field calculation order I need to move. 

 

When the form is blank, why is there 0.NaN in the TOTAL HOURS field?

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

- In Prepare Forms mode go to More - Set Fields Calculation Order.

- It doesn't do that in the new file. This was the bug in the old version. Re-applying the code should have solved it.

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

Thank you- that worked.

But I still have the 0.NaN appearing in the blank form.

Is there a way to do this:

We have a 4 hour min.  If 2 hours are entered, i.e. IN 8:00 OUT 10:00, can the TTL field round up to 4 hours?

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

The fields are not empty. They have a single space in them. If you clear them entirely this weird message will disappear. It's also possible to handle this situation in the code, but it will make it more complex.

 

Setting a minimum of 4 hours can be done by adding the following line:

if (hourDiff<4) {hourDiff = 4; minDiff = 0;}

if (minDiff.toString().length == 1) minDiff = '0' + minDiff;

 

 

 

 

 

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

ok- I will try that.

 

I assume the 4 HR MIN code is to be added to the existing code?  If so, is there a particular place I need to insert it?

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

Yes, before the second line, which I copied from the original 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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

Like this?

 

var start = this.getField("TIME1").valueAsString;
if (hourDiff<4) {hourDiff = 4; minDiff = 0;}

if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
var finish = this.getField("TIME2").valueAsString;

if (start=="" || finish=="") event.value = "";
else {
var startArr = start.split(":") ;

if (hourDiff<4) {hourDiff = 4; minDiff = 0;}

if (minDiff.toString().length == 1) minDiff = '0' + minDiff;

var finishArr = finish.split(":") ;

var hourDiff = finishArr[0] - startArr[0];

if (hourDiff<0) hourDiff+=12;

var minDiff = Math.floor(((finishArr[1] - startArr[1]) / 60)*100);
if (minDiff<0) {hourDiff--; minDiff += 100;}

if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
event.value = hourDiff + "." + minDiff;
}

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 ,
Apr 21, 2022 Apr 21, 2022

Copy link to clipboard

Copied

No, like this:

 

var start = this.getField("IN1").valueAsString;
var finish = this.getField("OUT1").valueAsString;
if (start=="" || finish=="") event.value = "";
else {
	var startArr = start.split(":") ;
	var finishArr = finish.split(":") ;

	var hourDiff = finishArr[0] - startArr[0];
	var minDiff = Math.floor(((finishArr[1] - startArr[1]) / 60)*100);
	if (isNaN(hourDiff) || isNaN(minDiff)) event.value = "";
	else {
		if (minDiff<0) {hourDiff--; minDiff += 100;}
		if (hourDiff<0) hourDiff+=12;
		if (hourDiff<4) {hourDiff = 4; minDiff = 0;}
		if (minDiff.toString().length == 1) minDiff = '0' + minDiff;
		event.value = hourDiff + "." + minDiff;
	}
}

 

I also added code to fix the other issue with the space.

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 ,
Apr 22, 2022 Apr 22, 2022

Copy link to clipboard

Copied

LATEST

Thank you. Worked perfectly.

 

I am grateful for your time.

 

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