VBA to fill Tbl B columns that match row values from Tbl A

  • Hello All,


    I'm attempting to restructure a table with another table.

    How would you do the following???

    All Clock IN's from TblA in TblB Clock In column and Clock OUT's in the Clock OUT columns

    This would also need to line up the appropriate Employee ID. I've also attached a macro enabled example file for testing


    TblA

    Time IN or OutEmployee IDINorOUT
    10/9/20 6:00 AM1212Clock IN
    10/9/20 6:30 AM2323Clock IN
    10/9/20 6:00 PM1212Clock OUT


    TblB

    Employee IDClock INClock OUT
    121210/9/20 6:00 AM10/9/20 6:00 PM
    232310/9/20 6:30 AMetc..
    etc..etc..etc..


    I appreciate any help with this.

    Files

    • ClockTest.xlsm

      (32.06 kB, downloaded 65 times, last: )
  • Hi Roy,


    Thanks for taking a look into this

    All of the data is queried into TblA


    I'd like the end result to be TblB so that we can easily see the hours worked in a shift.


    Desired result would be TblB format but would also accommodate for day and night shifts and indicate if someone didn't clock in or out:


    Employee IDClock InClock OutDuration (hrs)
    121210/01/20 6:00 PM10/02/20 5:00 AM11
    232310/2/20 4:00 AM10/2/20 1:00 PM9
    343410/2/20 8:00 PM10/3/20 5:00 AM9
    232310/4/20 12:00 AMNo Clock In


    Please let me know any more info I can provide

  • Hi Roy,


    They clock in using a JotForm which saves to a Google Sheet which is then queried to an Excel Workbook.

    That query populates TblA. TblB needs to be structured like the example in my last post so that the user can run a report from the dashboard which uses an Advanced filter macro.


    The value of the entire doc is based on the structure and accuracy of TblB


    I posted an example workbook with no connections to just show the data and an example of how I need TblB structured.


    - Show Clock in and out times for each employee ID

    - Accommodate for day and overnight shifts (e.g. 8/3/20 7:00 PM - 8/4/20 5:00 AM)

    - Show a blank or short message when employee forgets to clock in or out (e.g. "No Clock IN" - 8/4/20 5:00 AM, or 8/3/20 PM - "No Clock OUT")

  • Can't you get JotForm to export directly to Excel?


    Your method is really complicated and could easily lead to errors, which you really do not want with time keeping.

  • We have a lot of red tape with permissions and such.

    It would be simpler to use an all excel method with the clock in and out in the workbook. But that just isn't going to be available.


    We are able to set up a simple In & Out on jot form on a thin client computer, then HR has access rights for office to use the excel file from office computers.


    Later today when I'm at work I could attach the actual doc so you can see how it's put together; and hopefully give your feedback.


    Do you use excel to keep track of your employees? If so, is the data sent from a form? And would you mind sharing the method?

  • We use a time & attendance system , the employees fob in and out. Hours are downloaded in a csv file which can be uploaded to the payroll software. I doubt that your system is very secure and will be prone to errors.

  • Hi Roy,


    Attached is the Workbook with the Google Sheet connection. I turned off Refresh on Open


    Note, the JotForm form is simply:


    Enter Employee ID

    Clock IN

    Clock OUT


    (In and out are radio buttons)


    Also, the formulas were working fine for day shifts, but now that there are night shifts they are not properly capturing the shift.


    So, all the pay periods before last week were correct and were able to be emailed to payroll, but last week, period between 10-4 and 10-10 everyone's time is messed up.

  • Hi Sleeplol,


    You're welcome. Glad to hear your feedback. I just would like to help everyone who are having problem on Excel VBA.The experiments that I have is from some Excel forum on the Internet and I am still improving my excel skills when helping you.


    Regards.

    HOPE IT HELPS :):):)

    THANKS,

    VIETNT

    ADD ME AT SKYPE: viet.nguyen2992