You are viewing limited content. For full access, please sign in.

Question

Question

Calculating Working Hours - Timesheet

asked on December 10, 2020

Hi Guys!
 

I am creating a Timesheet process and trying to automatically work out the time worked from start time & finish time manually input from the user. 

I have added the following formula:

=(INDEX(Timesheet.FinishTime_1,ROW())-INDEX(Timesheet.StartTime_1,ROW()))

which I realised brings back the time difference in seconds, so I called this field 'Total Seconds'. So I created another field called 'Convert to Hours' which I was hoping to add this formula to:

=(INDEX(Timesheet.Total_Seconds,ROW())/3600) to get the time converted to hours (works in a calculator) but unfortunately I end up with the figure below:

 

 

The Total Hours field above we have got working with JavaScript but we are still working on having this work for each row. So if anyone has done this before and can tell me how to achieve this by the formulas or JavaScript then it would be much appreciated!

 

Thanks
Tina

1 0

Answer

SELECTED ANSWER
replied on December 11, 2020 Show version history

I have done this using the TIMEVALUE function which gets you away from using JS.

In this case, the user selects the start and end time, and puts in the lunch period in minutes which I calculate into a single variable in Total for total hours

After subtracting start from end which provides a Fraction of the day, I multiple by 24 to get hours. Lunch is calculated as minutes div by 60 to create hour fraction for lunch which I subtract from the Hours worked

The Total formula looks like this and requires no JS to support

=SUB(MULT(SUB(TIMEVALUE(INDEX(TimeTable.End,ROW())),TIMEVALUE(INDEX(TimeTable.Start,ROW()))),24),DIV(INDEX(TimeTable.Lunch,ROW()),60))

FYI, I set the start and end times Default times for the Start and End times, and set a range for Lunch minutes

 

@SteveKnowlton

1 0
replied on December 13, 2020

Thank you. This worked a treat!

0 0

Replies

replied on December 11, 2020

Hello Tina,

Regarding the total hour issue, you can consider looping the table each time the column related to the total hour is changed in order to update the value.

you can use the .change() method on the table or on the field itself to capture a change action. I've never done it before but your code will be something like this:
 

$(".tableClass").change(function(){

   $(".tableClass tr").each(function(){
     let startTime = //startTimeValue
     let FinishTime = //FinishTimeValue

     if(startTime != "" && finishTime !=""){
        
        let totalHoursValue = //computeNumberOfHours      
        $(this).find(".totalHoursFieldClass input").val(totalHoursValue)

     }
     
   })

})

Instead of looping through the whole table as well, you can also reach the last row of the table each time you are adding a new start and finish time and update its value accordingly. You can something like .last() to reach the last row of which you are adding the value. 

One more thing, I think (not able to test right now) you can use excel formula to do the job as well by allowing it to calculate the total hours based on the first two column of the table. I wish I can test it for you.  In that case, it will be more efficient since you are not running Javascript on each change in the  table. 

I hope i was able to help you with one of your concerns.

Joseph

0 0
replied on December 13, 2020

Thank you very much for your answer. It was much appreciated but I have managed to achieve this with the formula supplied and will stay away from using javascript.

0 0
You are not allowed to follow up in this post.

Sign in to reply to this post.