Copy link to clipboard
Copied
Hello
I am needing a script to calculate the hours worked for a single day. The only scripts I have been able to find in the community include dates. I have tried modifying those scripts but have not had any luck.
The employee will clock in when they arrive to work. They will clock out for lunch. Then clock back in when they return from lunch, and finally clock out when they leave for the day. The clock in times will be in “h:mm tt” format. They hours worked will display to the right and total hours will display at the bottom.
I am providing a screen shot with dummy text to display exactly what I need.
Hourse-Worked-1, Hours -Worked-2 and TOTAL have a custom script format of:
event.value = util.printf("%,001.2f", event.value / 60);
Can anyone help?
In that case,just use "value is the" in 'total' field and pick fields you want to sum and go to hoursWorked field->format tab and copy code from there and input it into same place in 'total' field and see if that helps.
Copy link to clipboard
Copied
See if this post can help you:
Copy link to clipboard
Copied
Thank you Nesa for your reply. I attempted to insert that script. However, I am not sure how that particular script would be implemented into my form. I am very new to this. I need to see what script goes in each form field.
Copy link to clipboard
Copied
In what format do you enter time into field?
Why use this: event.value = util.printf("%,001.2f", event.value / 60);?
How you want to show total time 7.5 or 7:30?
Copy link to clipboard
Copied
1. Both the time in and time out fields are h:MM tt format.
2. I was using it because I got it from a script that I found from another conversation. I don't really understand it and it may not be necessary.
3. Total time should be in the 7.5 format example. We are wanting to show a percentage in decimal form. That would be seven and a half hours. Example 7.56 hours or 4.42 hours.
Copy link to clipboard
Copied
1) What I meant was, do you write in field "2:30 pm" or just 2:30? I ask because if you enter 2:30 it will auto show am but if you enter 14:30 it will show 2:30 pm. I ask because calculation is different if you manually enter am/pm.
2)You don't need that.
3) What do you mean by percentage?
Copy link to clipboard
Copied
The employee will write in "2:30 PM" or "8:26 AM"
I apologize. I did a horrible job of explaining the second part. If the employee has a start time of 7:23 AM and End Time of 12:07 PM, the Hours-Worked-1 should show 4:44
In the second row: If they have a start time of 1:07 PM and end time of 4:31 PM the Hours-Worked-2 column will show 3:24
The TOTAL for the day would be 8:08.
Here is a shot of how I have it working in Excel.
Copy link to clipboard
Copied
This is not an easy task, have you considering hiring someone to do the job for you?
Copy link to clipboard
Copied
Copy link to clipboard
Copied
In your post above you said and in your excel photo it shows that you calculate total as 8:08 and in your file it's as decimal.So which one is it? Because 8:08 as decimal will show as 7.68.
Copy link to clipboard
Copied
The total hours for the day will be a decimal. The hour and minutes will go to the right of their clock in and times.
The example file I just uploaded works perfectly except it won't output unless you input a date. There also needs to be an additional clock in and out row.
Copy link to clipboard
Copied
If you don't need "Date" in your code replace cStartDate and cEndDate variables like this:
var cStartDate = util.printd("dd-mmm-yyyy", new Date());
var cEndDate = util.printd("dd-mmm-yyyy", new Date());
it will act as current date and will replace need for date field.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
You forgot document level script from your previous file.
Copy link to clipboard
Copied
Copy link to clipboard
Copied
Like I said, you are missing document level script from your previous file.
I added it to your file:
https://drive.google.com/uc?export=download&id=1eyLI4sim8wCn6__iT6Ay9bjGkoLtVcsR
I also changed field names in script for second row of fields, you can't just use same script in all fields, you need to change field names also.
Another thing, for total field I don't think same script will work for you.
Copy link to clipboard
Copied
I can't thank you enough! If you can think of a script that will calculate the two hours worked fields in the TOTAL box, you will be the biggest rock star ever.
Copy link to clipboard
Copied
How you want to calculate total? for example what should result look of 4:44 and 3:24?
Copy link to clipboard
Copied
I intially thought it should be in decimal form. but looking into it further. the Hours and Minutes format works better
The total should be the "4:44" or "3.24" format.
Copy link to clipboard
Copied
I meant what should sum result of 4:44 +3:24 look like, 8:08 or something else?
Copy link to clipboard
Copied
I'm sorry, Nesa. Yes. That is correct. Not something else.
Copy link to clipboard
Copied
In that case,just use "value is the" in 'total' field and pick fields you want to sum and go to hoursWorked field->format tab and copy code from there and input it into same place in 'total' field and see if that helps.
Copy link to clipboard
Copied
That did it. You are so awesome! I can't thank you enough!
Copy link to clipboard
Copied
Copy link to clipboard
Copied