Copy link to clipboard
Copied
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
Copy link to clipboard
Copied
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.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
The space means you can't use those field names in a simplified field notation, but you need a custom script anyway. You'll find the answers here:
Copy link to clipboard
Copied
I tried to go to those forums and links and nothing is showing up
Copy link to clipboard
Copied
For better look, i think this is simple but java isn't something i've quite understood yet. Just a start (HH:MM) "SUNStart" and then and end time (HH:MM) "SUNEnd" to equal total deciaml hours on the 3rd row. so i can total for the week.
This is a time sheet to record hours for staff - they don't need to calculate just add the hours
Copy link to clipboard
Copied
Do you want to show total hours as HH:MM or decimal?
Copy link to clipboard
Copied
Decimals preferably
Copy link to clipboard
Copied
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 = "";}
Copy link to clipboard
Copied
Thank you! This is working but also not and it's now so above my head. It's not calculating in some feilds or adding a runnign total
Copy link to clipboard
Copied
The issue was hours field names I didn't see correct and assume they are "TUEHOURS" and "THUHOURS" but it was "TUES" and "THURS", anyway I updated script to those names so try now.
Copy link to clipboard
Copied
Enter the following custom calculation script in the Sunday total hours field and change the field names for the rest:
var start=this.getField("SunStart Time").value;
var end=this.getField("SunEnd Time").value;
if(start=="" || end=="")
{event.value=""}
else
{
var hours=Number(end.split(":")[0])-Number(start.split(":")[0]);
var mins=Number(end.split(":")[1])/60-Number(start.split(":")[1])/60;
event.value=hours+mins;
}