JavaScript to calculate hours between a start time/date and end time/date

[Jesse29509303rqyg]

I've looked all over in the community forums and tried a few things that people suggested 

I am having trouble finding the right script to calculate hours between a start date/time and the end date/time. 

I am not sure if the problem lies with the input fields or the spot where I am inputting the script

I never have to work with scripts - just need some help this one time. 

[try67]

The first thing to do is to remove the Number format setting you used for the Total Hours field, since the result it's supposed to show is not a number, but a string.

Then you need to take into account the date fields you've created, and add them to your code (instead of 01/01/1970...).

Also, you need to take into account what happens if any of the four input fields are empty. You'd probably don't want to display anything in that situation.

After you've taken care of all of that see what output your code produces, and if it's incorrect post back here.

[Jesse29509303rqyg]

Hi thanks - I think I made those changes as you suggested. I am getting something to show in the Total Hours field but not what I intended

[try67]

You only applied the first change I mentioned.

[try67]

This is what I meant...

var timefinished = this.getField("TIME.FINISHED").valueAsString;
var timestarted = this.getField("TIME.STARTED").valueAsString;

var datefinished = this.getField("StartDate").valueAsString;
var datestarted = this.getField("EndDate").valueAsString;

if (datefinished && datestarted && timefinished && timestarted) {
	var datetimefinished = util.scand("yyyy-mm-dd HH:MM", datefinished + " " + timefinished);
	var datetimestarted = util.scand("yyyy-mm-dd HH:MM", datestarted + " " + timestarted);

	var difflnMilliSeconds = Math.abs(datetimefinished - datetimestarted)/1000;

	// calculate hours
	var hours = Math.floor(difflnMilliSeconds / 3600) % 24;
	difflnMilliSeconds -= hours *3600;

	// calculate minutes
	var minutes = Math.floor(difflnMilliSeconds / 60) % 60;
	difflnMilliSeconds -= minutes * 60;

	// set field value to the difference
	event.value = hours + ":" + minutes;
} else event.value = "";

[Jesse29509303rqyg]

We’re getting close:

I get Syntax Error: missing } in compound statement 1: at line 2

[try67]

Post the code as text. It's impossible to debug it like that. Also try to format it properly.

[Jesse29509303rqyg]

Copied and pasted directly from this forum into the Create and Edit JavaScripts field. I also did a carriage return after each of the ;

also, what do you mean by correct formatting? I have no experience with JavaScript

[try67]

That's not possible. The code I posted does not contain such an error. You must have modified it in some way, or didn't copy it in full. Post it here (AS TEXT!) and we'll help you solve it.

There are plugins that can help you with formatting your code, such as JSTool for Notepad++.

[Jesse29509303rqyg]

var timefinished = this.getField("TIME.FINISHED").valueAsString; var timestarted = this.getField("TIME.STARTED").valueAsString; var datefinished = this.getField("StartDate").valueAsString; var datestarted = this.getField("EndDate").valueAsString; if (datefinished && datestarted && timefinished && timestarted) { var datetimefinished = util.scand("yyyy-mm-dd HH:MM", datefinished + " " + timefinished); var datetimestarted = util.scand("yyyy-mm-dd HH:MM", datestarted + " " + timestarted); var difflnMilliSeconds = Math.abs(datetimefinished - datetimestarted)/1000; // calculate hours var hours = Math.floor(difflnMilliSeconds / 3600) % 24; difflnMilliSeconds -= hours *3600; // calculate minutes var minutes = Math.floor(difflnMilliSeconds / 60) % 60; difflnMilliSeconds -= minutes * 60; // set field value to the difference event.value = hours + ":" + minutes; } else event.value = "";

[try67]

Because you didn't format the code properly the first comment is also affecting the part of the code that's after it, so the entire last part of the code is effectively commented out. Do not delete any line breaks when you copy the code.

[Jesse29509303rqyg]

So it has to be typed exactly as you did it, to include where the carriage returns are?

[try67]

Yes, although you don't need to re-type it. Copying & pasting should work just fine.

It does for me:

[Jesse29509303rqyg]

Ok, so I get a time in the Total Hours box now.  How do I format the JS code to recognize military time and a transition from one day to the next?  For example, if someone works from 0700 on one day to 0730 the next day?

And also to calculate the minutes as hundreths vs minutes?

[try67]

> How do I format the JS code to recognize military time and a transition from one day to the next?

The code above does all of that already.

> And also to calculate the minutes as hundreths vs minutes?

Divide the final minutes value by 60, and then add it to the hours total.

[Jesse29509303rqyg]

Is this formatted correctly?

If not, please advise. The only thing I can see is that I am still missing something with the formatting, or there is an issue with the input fields.

[try67]

What is?

[Jesse29509303rqyg]

Sorry, the files didn't attach. 

[Jesse29509303rqyg]

Any feedback on this?

[try67]

Set the Format of the total hours field to None and change this line:

event.value = hours + ":" + minutes;

To:

event.value = hours + (minutes/60).toFixed(2);

[Jesse29509303rqyg]

Thanks - I made that change:

I also set the format for Total.Hours to number, because I received an error that said that the format was incorrect or something for that field.

Testing the script:

07:00-07:30 on the same day yields a correct answer: 0.50

07:00-08:00 on the same day yields an answer of 10.00

07:00-07:00 from 4/21/23 to 4/22/23 yields 0

[Jesse29509303rqyg]

Hi - looking for a follow-up.  Please see attached.  Between the hours of 0700 and 1700 today, the script calculates 90.98 hours

[Jesse29509303rqyg]

In addition to a script that works, how do the input fields need to be formatted?

[Jesse29509303rqyg]

Lastly, what kind of fields do the input fields have to be (text, date)?

[Nesa Nurani · Accepted solution]

You have wrong fields in 'datestarted' and 'datefinished' variables.

Try this:

var timefinished = this.getField("TIME.FINISHED").valueAsString;
var timestarted = this.getField("TIME.STARTED").valueAsString;

var datefinished = this.getField("EndDate").valueAsString;
var datestarted = this.getField("StartDate").valueAsString;

if (datefinished && datestarted && timefinished && timestarted) {
	var datetimefinished = util.scand("yyyy-mm-dd HH:MM", datefinished + " " + timefinished);
	var datetimestarted = util.scand("yyyy-mm-dd HH:MM", datestarted + " " + timestarted);

event.value = (datetimefinished - datetimestarted)/(60 * 60 * 1000);
} else event.value = "";