• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

I need to calculate hours worked for a single day - timesheet

Explorer ,
Apr 23, 2021 Apr 23, 2021

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?

timesheet-shot.png

 

 

TOPICS
How to , JavaScript , PDF forms

Views

7.6K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines

correct answers 1 Correct answer

Community Expert , Apr 26, 2021 Apr 26, 2021

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.

Votes

Translate

Translate
Community Expert ,
Apr 23, 2021 Apr 23, 2021

Copy link to clipboard

Copied

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 23, 2021 Apr 23, 2021

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Apr 23, 2021 Apr 23, 2021

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 23, 2021 Apr 23, 2021

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Apr 23, 2021 Apr 23, 2021

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?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 23, 2021 Apr 23, 2021

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.

 

excel-example.jpg

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Apr 23, 2021 Apr 23, 2021

Copy link to clipboard

Copied

This is not an easy task, have you considering hiring someone to do the job for you?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 23, 2021 Apr 23, 2021

Copy link to clipboard

Copied

Here is a form that almost has what we need. The date field would be removed.

 

There would be two start and end rows with  a h:MM field at the end

 

and the total would be at the bottom. This is the javascript I tried to modify. Ignore the fields below the clear button.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Enthusiast ,
Apr 23, 2021 Apr 23, 2021

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 26, 2021 Apr 26, 2021

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.

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 26, 2021 Apr 26, 2021

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.

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

I have changed those variables but still not getting a calculation.

I am uploading my file so you can see the javascript I have added.

form.jpg

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

You forgot document level script from your previous file.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

Please understand I am a complete newb when it comes to this. I have added the scripts to the rest of the fields. What am I missing?

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 26, 2021 Apr 26, 2021

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 26, 2021 Apr 26, 2021

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

How you want to calculate total? for example what should result look of 4:44 and 3:24?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 26, 2021 Apr 26, 2021

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.

 

 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

I meant what should sum result of 4:44 +3:24 look like, 8:08 or something else?

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

I'm sorry, Nesa. Yes. That is correct. Not something else.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Community Expert ,
Apr 26, 2021 Apr 26, 2021

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Apr 26, 2021 Apr 26, 2021

Copy link to clipboard

Copied

That did it. You are so awesome! I can't thank you enough!

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 11, 2022 Nov 11, 2022

Copy link to clipboard

Copied

Hi Nesa,

 

I am working on a similar task and was hoping you can advise how you were able to solve I have attached a smiliar task for your viewing. I would truly appreciate you help.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
New Here ,
Nov 10, 2022 Nov 10, 2022

Copy link to clipboard

Copied

I am working on a smiliar task woulfd you be able to shre with me the completed funtion? 

 

The good drive is not working - This is exactly what I am trying to figure out 

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines