Calculate working hours

Ask for Help
1

Hi, I want to calculate working hours duration by subtracting the office departure time from the office arrival time. However, the result doesn’t seem accurate when referring to hourly calculations. For example, if I clock in at 8:15 and clock out at 16:00, the result is 7.85 hours.

image
image1299×263 22.5 KB

2

It’s accurate based in the numbers you are using.
16-8.15=7.85

You are calculating math using decimal numbers. You are not using date/time numbers. If you used date/time columns to calculate time difference, then it would be accurate. Or your decimal based times should be 100 based instead of 60 based, so 8,15 would actually be 8,25.

3

Hey there here is some Javascript that should work out the difference between the two times even though they are in a decimal format. This returns the difference in hours,minutes like the initial number. You just have to change p1 to the initial time and p2 to the end time. Only issue if the difference in minutes are exactly 0 it will return 0 and not 00

function parseTime(timeStr) {
    // Split hours and minutes, then use parseInt to convert safely, with defaults of 0
    const [hoursStr, minutesStr] = timeStr.split(',');
    const hours = parseInt(hoursStr, 10) || 0;
    const minutes = parseInt(minutesStr, 10) || 0;
    const date = new Date();
    date.setHours(hours, minutes, 0, 0); // Set hours, minutes, seconds, and milliseconds
    return date;
}

  
const time1 = parseTime(p1.toString());
const time2 = parseTime(p2.toString());

// Calculate the difference in milliseconds
const diffMs = time2 - time1;

// Convert milliseconds to total minutes
const diffMinutes = diffMs / (1000 * 60);

// Convert minutes to hours and minutes
const hours = Math.floor(diffMinutes / 60);
const minutes = Math.floor(diffMinutes % 60);

return hours + "," + minutes
1 Like
4

“Please help. Essentially, I have clock-in and clock-out times, and I want to round these times. For example, if the clock-in time is 08:09, it will be rounded to 08:10 (the rounded time). I use an IF validation: if the clock-in time is before 08:00, it will record as 08:00; if it’s after 08:00, it will record the rounded time, which is 08:10. I will use this result to calculate the hourly wage.”

5

How are you ending up with times in decimal form?

6

I use format date

image
image724×502 26.9 KB

7

"Okay, I have found a way to get the time in hours, but another issue has come up. When I try to add the result of the multiplication, the result isn’t as expected. If I multiply the durasi jam with the gaji perjam, the result is incorrect. How can I accurately calculate the jam result so the calculation is correct?

Note:

  • Entry time is (kolom Rounded jam masuk)
  • Exit time is (kolom rounded jam pulang)
  • Subtraction of exit time minus entry time = (kolom durasi jam untuk perhitungan gaji)
  • Hourly wage is (kolom gaji perjam)
  • Calculated hourly wage result is (kolom hitung gaji perjam)"

image
image894×221 17 KB

8

You would need to convert the minutes of time difference to a % so 1 hour 30 minutes would be 1.5 as 30 is 50% of 60 when multiplied together with your hourly wage this should provide the correct value

9

“Sorry, I didn’t quite understand how to convert minutes to a percentage. If you don’t mind, could you show a screenshot?”

10

So if I clockout at 08:16, does it round to 08:20 or 08:10?

11

08:10

12

For example. If the entry time was 8:15 and exit was 16:00 the difference would be 7 hour 45 minutes however to use this time with your hourly wage you need to convert it minutes/60 in this case 45/60 = 0.75 so the number you would multiple with your hourly wage would be 7.75 instead of 7:45.
image

image
image345×155 3.06 KB

As shown above using that formula you should be able to convert your time into a decimal number which can be used to workout the final wage

1 Like