Help with calculating total working hours from start and end time inputs

💬 App Building
1

Hi all,

I’m building a simple internal tool in Retool where users input Start Time and End Time using time pickers. I want to calculate the total working hours (including partial hours like 7.5) and show it in a text component or store it in a DB.

I’ve tried basic JS like:

const start = moment(startTime.value, "HH:mm");
const end = moment(endTime.value, "HH:mm");
const duration = moment.duration(end.diff(start));
const hours = duration.asHours();

But it sometimes returns negative values when the end time is past midnight.

What’s the best way to calculate total hours across midnight (e.g. 10:00 PM to 6:00 AM)?

Any help would be really appreciated! :sparkling_heart:

Jhonn Marie

2

Hi @jhonnmarie,

Try this:

const start = moment(startTime.value, "HH:mm");
const end = moment(endTime.value, "HH:mm");

// If end is before start (i.e. past midnight), add 1 day to end
if (end.isBefore(start)) {
  end.add(1, 'day');
}

const duration = moment.duration(end.diff(start));
const hours = duration.asHours();

// Round to 2 decimal places
const roundedHours = Math.round(hours * 100) / 100;

return roundedHours;
4

I just thought I'd share a more efficent version (I haven't ran tests on this one but the logic stands I think), for any future readers who are concerned about the efficiency of repeatedly conducting such a task (like in a loop or something):

const start = moment(startTime.value, "HH:mm");
const end = moment(endTime.value, "HH:mm");

// Add a day if end time is before start time (overnight)
if (end.isBefore(start)) {
  end.add(1, 'day');
}

// Calculate hours difference and round to 2 decimal places
 return Math.round(end.diff(start, 'hours', true) * 100) / 100;

or, if you're a masocist, there's a one-liner (slightly less efficent than above, but it's highly dependent on the JIT compiler/version as complex conditionals can occasionally confuse it resulting in less efficent opcodes/machine code... its a tiny difference unless this is ran MANY times in a loop or something but it's nice to have the option imo)

return Math.round(((end = moment(endTime.value, "HH:mm")).isBefore(start = moment(startTime.value, "HH:mm")) && end.add(1, 'day'), end.diff(start, 'hours', true)) * 100) / 100;
3 Likes