How can I calculate work time using the Input Table field

  • Profile Image
    jarrodl
    Asked on October 15, 2020 at 07:16 AM

    Hi Team,


    I was wondering is there a way to calculate work time in jotform using a combination of widgets and conditional logic.


    The image attached is what I would like to be able to achieve.

    These needs to be very simple. So if it can be in 12h time that would be prefect.



    Screenshot
  • Profile Image
    Michael
    Answered on October 15, 2020 at 08:23 AM

    Please allow me some time to check if there is a workaround we can do to meet the requirements that you have explained.

    I will be updating you on this ticket with my findings and/or a demo form as soon as possible.

  • Profile Image
    Jarrod 
    Answered on October 16, 2020 at 08:53 AM

    Hi Mike,


    Have you been able to make any progress? It's just this is time-sensitive.

    It doesn't matter how complex the backend of the form is, I just need the User experience to be really simple.


    Regards,

    Jarrod

  • Profile Image
    John
    Answered on October 16, 2020 at 10:18 AM

    Hello Jarrod - It would be easier I think if we'll use a 24hr format, but let's see if we can find a much easier workaround to use 12hr format.

    I am also contacting my colleague if there's progress on his demo form.

    We'll notify you here once it is available.

  • Profile Image
    John
    Answered on October 17, 2020 at 12:58 AM

    Thank you for waiting! I was able to make it work using a 12hr format. However, instead of using a free text input, I used dropdowns in the table and added pre-defined options for the time:

    1602905600_5f8a660082384_

    I set the input table's input type as "MULTI-TYPE COLUMN" (3). Then set the Clock In and Clock Out columns as dropdowns (4).

    I then used time figures in 12 hr format as dropdown options, so the user can just select the time (5).

    Next, I added two form calculation widgets to extract the value of "CLOCK IN" and "CLOCK OUT" fields. It would be 2 widgets for each day:

    1602905804_5f8a66cc3dd51_

    Lastly, I created 9 conditions to perform the calculation, for Monday alone. So that means you'll need to create the same 9 conditions for each day of the week. You can just clone each condition, replace the fields with the corresponding day of the week. Here's a preview of the conditions.

    GUIDE YOU CAN USE: How-to-Insert-Text-or-Calculation-into-a-Field-Using-Conditional-Logic

    And here's how it works now:

    1602906413_5f8a692de4dd3_sadsadsad.gif

    Here's a link to the cloned form so you can try: https://form.jotform.com/202894665333967

    You can clone it in case you would like to look into the conditions I created. Here's a guide on how-to-clone-an-existing-form-from-a-url.

    Please try that and let us know if you need further assistance.

  • Profile Image
    Jarrod 
    Answered on October 17, 2020 at 01:42 AM

    Hi John,


    You've blown my mind. Thank you so much, I can see this must have taken a lot of time.

    I'm so thankful for your efforts.


    Regards,

    Jarrod

  • Profile Image
    jarrodl
    Answered on October 17, 2020 at 02:02 AM

    Hi John,

    Just one thing, Can we do the time interval of 30 minutes instead of hourly? Or can it only be done this way?


    Regards,

    Jarrod

  • Profile Image
    Michael
    Answered on October 17, 2020 at 10:58 AM

    I have created a demo form that calculates total work hours and has 30 minutes time interval instead of hourly as per the requirement you mentioned in your last reply.

    Heres the link to the Demo form:

    https://form.jotform.com/202903355087960

    I have just set up the calculation for Monday in that form and here how it works.

    1602942216_5f8af508d9592_zt201017_092815

    Respondents will select a value in the dropdown fields under the (a) Clock In, (b) Clock Out, and (c) Lunch columns of the Total Hours Worked Each Day Input Table field.

    The (d) Monday In field will get its value from the first input field under the (a) Clock In column of the Total Hours Worked Each Day Input Table field with the help of this condition.

    1602942922_5f8af7cab5608_zt201017_095455

    The (e) Monday Out field will get its value from the first input field under the (b) Clock Out column of the Total Hours Worked Each Day Input Table field with the help of this condition.

    1602942933_5f8af7d523994_zt201017_095446

    If the value in the (d) Monday In field contains "AM" AND the Total Hours Worked Each Day Input Table field is filled, the value in the (d) Monday In field will be passed to the first input field under the (g) Clock In column of the Results Input Table with the help of this condition.

    1602943421_5f8af9bd3a572_zt201017_100329

    If the value in the (d) Monday Out field contains "AM" AND the Total Hours Worked Each Day Input Table field is filled, the value in the (d) Monday Out field will be passed to the first input field under the (h) Clock Out column of the Results Input Table with the help of this condition.

    1602943503_5f8afa0f291d4_zt201017_100449

    If the value in the (d) Monday In field contains "PM" AND the Total Hours Worked Each Day Input Table field is filled, the value in the (d) Monday In field +1200 (12 hours)will be passed to the first input field under the (g) Clock In column of the Results Input Table with the help of this condition.

    1602943559_5f8afa4728dc1_zt201017_100547

    If the value in the (d) Monday Out field contains "PM" AND the Total Hours Worked Each Day Input Table field is filled, the value in the (d) Monday Out field +1200 (12 hours) will be passed to the first input field under the (h) Clock Out column of the Results Input Table with the help of this condition.

    1602943586_5f8afa623e878_

    Conditions #4, #5, #6, and #7 resolve the conflict that happens if you get the difference between two different hours of the day that belongs to different time periods.

    For example: 01:00 AM to 01:00 PM.

    Notice that the value under the (g) Clock In and (h) Clock Out columns of the Results Input Table are already in the 24-hour time format.

    And if we get the difference between the value in the (g) Clock In and (h) Clock Out columns we will get a value that is also in the 24-hour format. However, what we need is a time value that is in decimal. To resolve that, I have added the condition below to the form.

    1602944823_5f8aff37820e9_zt201017_102020

    The condition will convert the time from the 24-hour format to decimal. The condition also fixes the conflict that occurs when the selected time has 30 minutes interval. The result of the calculation in that condition will be inserted in the (f) Monday TH (base) Number field.

    The last two conditions below will calculate the value that will be inserted in the first input field under the (i) Total Hours Less Lunch column of the Results Input Table.

    1602945983_5f8b03bf1bb26_zt201017_104608

    The conditions are created not only to deduct the selected option in the first input field under the (c) Lunch column of the Total Hours Worked Each Day Input Table, but to also resolve the conflict if, in any case, respondents will select work hours that will cover hours on the current day and on the following day.

    For example: 08:00 PM (current day) to 02:00 AM (following day).

    I would suggest that you create a cloned version of my form above into your account so you can examine it. Also, when you check the conditions, hover over the tiles that represent an Input Table field, it will reveal what field in the table it is.

    1602946459_5f8b059beb793_zt201017_105220

    This is will be helpful when you create the conditions for the other Days of the week.

    Please give it a try and let us know if you need any further assistance.


  • Profile Image
    Jarrod 
    Answered on October 20, 2020 at 04:35 PM

    Am I able to update the times to be 15 Minute intervals with all the existing conditions still going to work the same way?

  • Profile Image
    John
    Answered on October 20, 2020 at 07:13 PM

    Hello Jarrod - Yes, it seems to work with the 15-min interval leaving the conditions provided intact. I just tested it now and it seems to work as intended:

    1603235545_5f8f6ed9dfb70_

    You may try it on your end as well by cloning the form and add 15-min intervals into the dropdown options of the Input Table.

  • Profile Image
    jarrodl
    Answered on November 16, 2020 at 04:55 AM

    Hi Team,

    Apologies to keep pestering you, I've been asked to factor in leave.

    What condition would need to be set so that if any leave option is selected in that row, then the total hours worked will not calculate the row and set it to a value of 0

    1605520256_5fb24b8005888_


    Also, am I able to setup conditions to only show the total hours worked, if a row has had data entered.

    1605520348_5fb24bdc11b06_

    For the image above, I've entered data for Tuesday, Week 1 & Wednesday week 2. So in the total Hours section underneath the table, I should only see the total hours field for the Tuesday & Wednesday that has data inputted.


    Regards,

    Jarrod

  • Profile Image
    Michael
    Answered on November 16, 2020 at 06:24 AM

    Please give me some time to work on a solution to your concern. I'll get back to you on this ticket as soon as possible.

  • Profile Image
    Michael
    Answered on November 18, 2020 at 10:23 AM

    Apologies for any delays. Here's an updated version of the form I have created above that is now based on the most recent requirements you have mentioned in your last reply.

    https://form.jotform.com/203223713299959

    Each Day now has a total of 11 conditions each to work (before 9). The conditions were also updated since I have added a "Leave" column on the first Input Table field

    1605709204_5fb52d94430fa_zt201118_091947

    and two additional fields for it to work with the equation.

    1605709253_5fb52dc50a520_zt201118_092033

    I also fixed the conflict I noticed with the original version of the form that happens when someone would select 12:00 PM. In the previous version, the 12 hours are being automatically added to that time since it contains PM. The fix requires that these conditions are arranged in this order so it will not conflict with each other. For the conditions created on each day, the set of conditions in the red box should always be placed on top of the set of conditions in the green box in the Condition Settings of the form.

    1605710539_5fb532cb49900_zt201118_092436

    I have also added the conditions that will calculate the total hours for Tuesday to show you that the Total Hours in a day will just show a zero value if the entire row for that day has not been completely filled yet.

    As for the condition for the Leave column, it will multiply the entire equation by 0 if any of the options from the dropdown is selected.

    Please give the latest version of the demo form I created above a try and let us know if you need any further assistance.

    You can create a cloned version of the form so you can inspect it on your end better.

    Reference Guide: How-to-Clone-an-Existing-Form-from-a-URL

  • Profile Image
    jarrodl
    Answered on November 24, 2020 at 04:12 AM

    Hey Team,


    I setup all the conditions but can't get the majority of them working.

    This is what the form looks like when I've put information in.


    1606208298_5fbccb2a3f095_


    1606208319_5fbccb3f27fcb_


    1606208998_5fbccde630eec_

    These fields seem be the issue that's preventing the form from working, But the conditions look correct.

    Along with this table

    1606209106_5fbcce52ed836_


    Regards

  • Profile Image
    Dagmar
    Answered on November 24, 2020 at 07:03 AM

    Hello,

    I started checking if all the conditions were set, ( on this form.https://www.jotform.com/form/203260352056142) and I noticed, that the second set of Tuesdays and Mondays is not there. I noticed it here first:

    1606219183_5fbcf5afc1c0d_

    Those days are also missing in the next set, here:

    1606219330_5fbcf64262c6d_

    And in all the other sections.
    Could you try adding them and then see if the form will work then?



  • Profile Image
    Dagmar
    Answered on November 25, 2020 at 04:24 AM

    Hello,

    I would like to apologize for missing those.

    The problem with the times seems to be that they are (except the first Monday and Tuesday) NOT transferred to the Total Hours part of the form-- making all other calculations not possible.

    1606296273_5fbe22d18c556_

    Please give me a bit more time to check the conditions.

  • Profile Image
    jarrodl
    Answered on November 28, 2020 at 08:54 PM

    Can I please get an update on this.
    I've spent the past few days going through conditions and everything looks correct.


    Regards

  • Profile Image
    Patrick_R
    Answered on November 29, 2020 at 03:21 AM

    Hi! Please allow me some time to look into this. I'll get back to you shortly with an update.

    Thank you!

  • Profile Image
    Patrick_R
    Answered on November 29, 2020 at 04:38 AM

    Hi! I believe that you're currently working on this form: https://www.jotform.com/form/203260352056142

    I see double entries for almost all conditions on your form along with some fields. I've shown this in the screencast below by comparing the form that we provided you and your form:

    1606642662_5fc36be6950e9_Screencast 2020

    Kindly clone my colleague Mike_G's form, i.e. https://form.jotform.com/203223713299959 into your account and work on that instead. This is to ensure that we start clean and no previous condition/field is left in the form to avoid confusion.

    Please give it a try. In case of any queries, let us know.

    Thank you!

  • Profile Image
    jarrodl
    Answered on November 29, 2020 at 05:44 AM

    You'll hear back from me in a few days. Setting up all those conditions took me a whole work day.


    Regards,


  • Profile Image
    Patrick_R
    Answered on November 29, 2020 at 06:49 AM

    Sure, We'll wait for your response.

    Thank you!

  • Profile Image
    jarrodl
    Answered on December 03, 2020 at 04:16 AM

    So I've setup all the conditions the except that big one.

    I've set the big on up on 3. Wednesday to test it all before moving onto the rest



    This condition is the condition that does all the calculations and gives the final output

    1606986732_5fc8abecdc57e_So after setting it up on 3. Wednesday, I noticed the following

    1606986801_5fc8ac31b6156_

    This isn't calculating correctly resulting in the total hours calculations field not calculating correctly. this is simpley due to the Results Table data being entered wrong.

    1606986884_5fc8ac845a951_


    I've structured the conditions better to help with navigating through all of them.

    This has become urgent and I'm starting to get some heat, So I really need to get this sorted ASAP.

    So once I can get Wednesday working correctly, then I'll move onto the rest of the big conditions.

  • Profile Image
    Jed_C
    Answered on December 03, 2020 at 08:25 AM

    I've made a cloned version of your form here https://form.jotform.com/203372694155963 and added the condition for Wednesday upto Sunday.

    If you want to add week 2, you can follow the same order on how I cloned the condition for each day on this video https://shots.jotform.com/jed/vid/2635495.mp4. I think you will need to download the vide as it's not playing on my Chrome browser so please click on the 3 dots beside the volume icon to download the screencast.

    1607001838_5fc8e6ee3fda9_Thread 2635495

    1607001849_5fc8e6f9ab267_Thread 2635495

    Let us know if you need further help.

  • Profile Image
    jarrodl
    Answered on December 03, 2020 at 03:37 PM

    I've got all conditions working but one.
    Every time I've bought up condition issues I'm getting told to clone and re-do a bunch of conditions.


    These are very time consuming.

    If you look at this form Waste Time Sheet V3 (jotform.com) you will see the extent of work I've already put into this. I'm not comfortable having to re-do a bunch of work.


    Please look though the 3. Wednesday condition set and help identify where the issue is.


    Regards

  • Profile Image
    Michael
    Answered on December 03, 2020 at 05:36 PM

    We would like to apologize for any inconvenience. Please allow me some time to check the issue on the set of conditions that calculates the value for the 3. Wednesday.

    I'll use a cloned version of the form you linked in your last reply so once I have provided you the solution, it will be easy for you to make changes to your form.

    I'll get back to you on this ticket as soon as possible.

  • Profile Image
    Michael
    Answered on December 05, 2020 at 03:11 AM

    Apologies for any delays. I was able to determine the issue with the form you linked in your last reply and I have applied the solution on a cloned version for your form. Here's the link to that form: https://form.jotform.com/203377081105953

    If you want you can just create a clone of that form in your account and use it instead.

    Reference Guide: How-to-Clone-an-Existing-Form-from-a-URL

    However, if you want to use your form instead so it will have the same form ID, please see the information below that explains the issue of why the value for "3. Wednesday" isn't working on your form.

    By the way, it is easy to view the conditions you made for each "day" if we use the Search bar of the Condition settings. And since we need the conditions for "3. Wednesday", we will type in the search bar — "3. W".

    1607150566_5fcb2be65c601_zt201205_014026

    The same goes for the other days of the week. For example, "1. M" for 1. Monday, "2. T" for 2. Tuesday, "13. S" for 13. Saturday on the 2nd week, and so on and so forth.

    As for the issue, we will focus on these 3 conditions. These also go for all the remaining days of the week that are still not working.

    1607150834_5fcb2cf29983b_zt201205_014638

    1607150850_5fcb2d02c7964_zt201205_014652

    1607150863_5fcb2d0fac422_zt201205_014701

    As for the first condition in the screenshots above, it passes the difference of the converted value under the Clock In and the Clock Out columns of the Results Table - Week 1 input table field to the Day TH (Total Hours)(base) Number field.

    1607153953_5fcb39215e504_zt201205_020339

    In my example above, the calculation happens when the "3. Wednesday In" AND the "3. Wednesday Out" fields are filled.

    1607152351_5fcb32df08e93_zt201205_021219

    As for the calculation, it has two parts.

    1607153156_5fcb3604c2d9e_zt201205_021823

    All the fields added to the first part (red) of the formula should refer to the cell under the Clock Out column of the Results Table - Week 1 input table field

    1607153678_5fcb380e63121_zt201205_023414

    while all the fields added to the second part (green) of the formula should refer to the cell under the Clock In column of the Results Table - Week 1 input table field.

    1607153689_5fcb38197a3e3_zt201205_023424For the other two conditions I mentioned above, you just need to make sure that the value being passed from the Day In and Day Out fields on the form to the cell under the Clock In and Clock Out columns of Results Table - Week 1 input table field is a "Number" (0-9), not "String" (A-Z). Also, the value should be passed to the correct cell in the Results Table - Week 1 input table field, respectively.

    Below is the condition for passing the value in the 3. Wednesday In field to the cell under the Clock In column, 3. Wednesday row of the Results Table - Week 1 input table field.

    1607154964_5fcb3d1492e77_zt201205_024956

    You just need to do the same when creating the condition for passing the value in the 3. Wednesday Out field to the cell under the Clock Out column, 3. Wednesday row of the Results Table - Week 1 input table field.

    Again, the information explained above should also help you when setting up the conditions for the rest of the days of the week in your form.

    Feel free to let us know if you need any further assistance.