Time difference calculation

[Philip Jones]

Hello,

I am in the process of evaluating this software as a replacement for a FileMaker db.

I have two date/time fields and I am trying to determine the time elapsed between the two fields.

I have set the two times as 2000h and 1500h. Clearly the difference between these two is 5h.

However, when I set up the calculation field, I get a time difference of 4:59?

Any help is appreciated. Thanks.

Phil

Attachments:

You must be logged in to view attached files.

[Sam Moffatt]

What happens if you change the formula result type from “number” to “date”?

[Philip Jones]

Unfortunately it didn’t give me the correct answer. It now says “Dec 31, 1969 at 23:59:59”. Thank you for trying to help.

[Brendan]

Setting the value to Number is correct in this situation because when you subtract two date fields, you get the number of seconds between those two dates.

Plus you have the Number Format set to show HH:MM which tells Tap Forms to convert the seconds to hours and minutes for you. And because the duration is a numeric value, setting the Result Type to Date will not work.

But your formula should work to give the correct results as far as my testing goes.

Try setting the Number Format to No Style. You should see a result value of 18000 for that duration.

What do you get?

[Philip Jones]

Thank you for the reply.

I do indeed see 18000 as the number of seconds having elapsed between those two times.

But I would like to see HH:MM as its format.

When I click on “Number Format” and choose Time (HH:MM) it is now working. Before, under what appeared to be identical conditions, it wasn’t. As per the screenshot above.

I have no idea why, absent perhaps some background precision issue not exposed to the user?

Thank you for your help.

[T.L. Ford]

I think what you are looking for is something like this:

The display field would be text, as it’s not an actual time, but elapsed time between two dates in HH:MM format.

function msTimeDiffString(duration) {

	const msDay = 86400000;
	const msHour = 3600000;
	const msMinute = 60000;
	const msSecond = 1000;

	var days = 0;
	var hours = 0;
	var minutes = 0;
	var seconds = 0;

	var remainder = parseInt(duration);

/*

	// you'll need this if you have days difference
	if (remainder > msDay) {
		days = Math.floor(remainder / msDay);
		remainder -= days * msDay;
	}
*/
	if (remainder > msHour) {
		hours = Math.floor(remainder / msHour);
		remainder -= hours * msHour;
	}

	if (remainder > msMinute) {
		minutes = Math.floor(remainder / msMinute);
		remainder -= minutes * minutes;
	}

	if (remainder > msSecond) {
		seconds = Math.floor(remainder / msSecond);
		remainder -= seconds * msSecond;
	}

	hours = (hours < 10) ? "&nbsp;" + hours : hours;
	minutes = (minutes < 10) ? "&nbsp;" + minutes : minutes;
	seconds = (seconds < 10) ? "&nbsp;" + seconds : seconds;

	return hours + ":" + minutes;
//	return hours + "h " + minutes + "m";
//	  return days + "d " + hours + ":" + minutes + ":" + seconds + "." + milliseconds;
}

var when1 // date per your data
var when2 // date per your data

var msDiff = when1.getTime() - when2.getTime();

// msDiff should be a positive number of milliseconds between dates.
console.log(msTimeDiffString(msDiff));

[T.L. Ford]

Oh, and you can change the   to 0’s or “”‘s for
04:30
4:30

The   probably isn’t right – I was copying from my website (html vs tap forms).

[T.L. Ford]

The original error is likely milliseconds and rounding.

[Philip Jones]

Thank you for this detailed reply, T.L. Ford!

[Author]

Posts