Timesheets Hours Calculation

12 replies
988 views
Translate

Hi there,

just curious if soemone can assist me with this calculation. I'm trying to get a do start time - end time or end time minus startime . which ever works to get a positive number. I thought i had it but i do not. Is this better in Custom or Simplified?

TIA

M

matteo_3141

Participant

Instead of short answers just visit this page where you will get complete guide on how to calculator working hours in excel:https://arbeitszeitrechnerprofi.de/arbeitszeit-berechnen-excel/

Translate

Nesa Nurani

Community Expert

Do you want to show total hours as HH:MM or decimal?

Translate

S

shondrahhhAuthor

Participating Frequently

Decimals preferably

Translate

Nesa Nurani

Community Expert

Put this as 'Custom calculation script' in "HOURS WEEK 1" field (remove any other calculation or script you have for those fields) :

function timeToMinutes(time) {
 if (!time) return 0;
 var [hours, minutes] = time.split(":").map(Number);
 return hours * 60 + minutes;}

var fields = [
 { start: "SUNStart Time", end: "SUNEnd Time", hours: "SUNHOURS" },
 { start: "MONStart Time", end: "MONEnd Time", hours: "MONHOURS" },
 { start: "TUEStart Time", end: "TUEEnd Time", hours: "TUESHOURS" },
 { start: "WEDStart Time", end: "WEDEnd Time", hours: "WEDHOURS" },
 { start: "THUStart Time", end: "THUEnd Time", hours: "THURSHOURS" },
 { start: "FRIStart Time", end: "FRIEnd Time", hours: "FRIHOURS" },
 { start: "SATStart Time", end: "SATEnd Time", hours: "SATHOURS" }
];

var totalMinutes = 0;

fields.forEach(field => {
 var startVal = this.getField(field.start).valueAsString;
 var endVal = this.getField(field.end).valueAsString;

 if (startVal && endVal) {
  var dayTotalMinutes = timeToMinutes(endVal) - timeToMinutes(startVal);
  dayTotalMinutes = Math.max(dayTotalMinutes, 0);
  totalMinutes += dayTotalMinutes;

  var dayDecimalHours = (dayTotalMinutes / 60).toFixed(1);
  this.getField(field.hours).value = dayDecimalHours;} 
 else {
  this.getField(field.hours).value = "";}});

if (totalMinutes > 0) {
 var totalDecimalHours = (totalMinutes / 60).toFixed(1);
 event.value = totalDecimalHours;} 
else {
 event.value = "";}

Translate

PDF Automation Station

Community Expert

What is the format of your time fields? You can't do it with a simplified notation and it looks like your time field names have spaces in the field names, but it's hard to tell with a screenshot.

Translate