Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    New Lounger
    Join Date
    Mar 2015
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question Macro to send data to master file from shop-floor file

    I am trying to improve an excel file that tracks hours worked by employees with the following characteristics. First I would like to add 10 minutes to formula which is in place in column E to compensate donning doffing. This formula currently calculates hours worked, but I still need to add 10 minutes to total hours worked. Secondly, if any of the following codes (DEA, DL, EA, FMLA, IOJ, IOW, JD, LT, ML, NCNS, PC, PH, PL, RE, SCHDOFF, SICK, SU, TE, TRNDT, UA, VAC1, VAC2) is selected under comments column, then remove automatically the start time and end time in column C and D. Thirdly, if VAC1 code is selected as a vacation day, display 4 hours of vacation and if VAC2 is selected, then display 8 hours under Total column. Finally, I would like to add a vba macro to copy data from A9 down to the last row of data in shop floor time and attendance file, and paste it into the Master Shop Floor file. Also copy and paste supervisor, PlantID, CostCenter, shift, and ProdDate information in its corresponding column in the Master Shop Floor file. Finally, the macro should prevent users from sending the data twice. This information should be sent once at the end of the shift.
    For better details I have attached the shop floor file and the master file.
    Thanks everyone for taking the time and effort to assist me in this challenge.
    Attached Files Attached Files

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Che,

    In your spreadsheet, column E measures time (hours) in decimal format (ex. 8.5 hours and not 8:30). If you can use time format then the formula in E9

    End time- start time -Lunch + 10 minutes
    =D9-C9-TIME(0,F9,0)+TIME(0,10,0) then copy down

    will return 8:10 in cell E9 with cell E9 having a custom format of h:mm

    HTH,
    Maud

    Che.png

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    if any of the following codes (DEA, DL, EA, FMLA, IOJ, IOW, JD, LT, ML, NCNS, PC, PH, PL, RE, SCHDOFF, SICK, SU, TE, TRNDT, UA, VAC1, VAC2) is selected under comments column, then remove automatically the start time and end time in column C and D.
    Placing the following code in the "TimeAndAttendance" sheet module will remove the start, end, and lunch times if any of the codes that you specified are entered in column G. The code uses the data validation entry as the criteria for a Vlookup on the "Codes" sheet.

    In the sheet module (Note: extend the range G9:G28 to the number of employees you have)
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Cells.Count > 1 Then Exit Sub
        If Not Intersect(Target, Range("G9:G28")) Is Nothing Then
            If WorksheetFunction.VLookup(Target, Worksheets("Codes").Range("A3:B34"), 2, False) = "remove" Then
                Cells(Target.Row, 3) = ""
                Cells(Target.Row, 4) = ""
                Cells(Target.Row, 6) = ""
            End If
        End If
    End Sub
    HTH,
    Maud
    Attached Files Attached Files

  4. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Thirdly, if VAC1 code is selected as a vacation day, display 4 hours of vacation and if VAC2 is selected, then display 8 hours under Total column.
    I have modified the code to accommodate the your third request. It will also place the formula back in column E if any data validation is selected that should require the formula to calculate a total.

    In the sheet module:
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Cells.Count > 1 Then Exit Sub
    '------------------------------------------
    'DETERMINE IF DATA VALIDATION SELECTED
        If Not Intersect(Target, Range("G9:G28")) Is Nothing Then
    '------------------------------------------
    'CHECK CODE SPECIFICATIONS TO REMOVE TIMES
        If WorksheetFunction.VLookup(Target, Worksheets("Codes").Range("A3:B34"), 2, False) = "remove" Then
            Cells(Target.Row, 3) = ""
            Cells(Target.Row, 4) = ""
            Cells(Target.Row, 5) = ""
            Cells(Target.Row, 6) = ""
    '------------------------------------------
    'REINSERT FORMULA BACK INTO COL E
        Else:
            Cells(Target.Row, 5).Formula = "=D" & Target.Row & "-C" & _
                Target.Row & "-TIME(0,F" & Target.Row & ",0)+TIME(0,10,0)"
        End If
    '------------------------------------------
    'CHECK VACATION SELECTION
        Select Case Target
            Case "VAC1"
                Cells(Target.Row, 5) = "4:00"
            Case "VAC2"
                Cells(Target.Row, 5) = "8:00"
        End Select
        End If
    End Sub
    Attached Files Attached Files
    Last edited by Maudibe; 2015-03-08 at 10:52.

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi Maud

    nicely done.
    I couldn't help but notice the cell colours for vacation and sick days.
    So I added the ability to set colour codes.

    In my attached example:

    Data is in named range block1
    There are hidden columns [I:M] which contain the columns for
    Supervisor, PantID, CostCenter, Shift, ProdDate

    You can set the cell [E6] to the allowance minutes.
    Note:
    0mins = 0.00hr
    10mins = 0.17hr
    12mins = 0.20hr
    15mins = 0.25hr
    etc etc

    You can click the top button to select the location of your Master Shop Floor file.
    You only need to do this once, unless you want to change it, or the file location changes.

    Each time you click the [Update Master..] button, the data block will be copied across, replacing any existing data for the same ProdDate.

    The colour for the cells in column [G] are as defined in the [AttendanceCodes] column.
    If you change these colours, they will be updated on the [TimeAndAttendance] automatically.
    When you click a code from the dropdown, the current colour for that code will be automatically set.
    If you don't want any colours, set the background cell colour to 'no fill' in the [AttendanceCodes] column.

    The routines make use of hidden columns [R] and [S]

    zeddy
    Attached Files Attached Files
    Last edited by zeddy; 2015-03-08 at 13:40.

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    ..I also added a spoken message to say DONE!

    zeddy
    Last edited by zeddy; 2015-03-08 at 13:43.

  7. #7
    New Lounger
    Join Date
    Mar 2015
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thank you very much Mr. Maud for your help on this matter.
    The reason I was inclining to use the decimal format and not the hour format is because Kronos (an electronic timekeeping system) adds up hours worked in a decimal format, and when I run a report from Kronos to compare the hours worked to the hours reported in the shop floor time and attendance file, I was getting confused by conversion of time. However, now that you pointed it out, I could simply divide the total hours worked by 24 in order to match the Kronos and hours reported.
    One thing I noticed after using your formula =D9-C9-TIME(0,F9,0)+TIME(0,10,0), and changing the start and end time example 3:15 PM to 1:15 AM for employees who work after midnight, Excel display the pound signs (###). Is there a way to make Excel display the total hours worked instead of the pound signs?
    I also have another worksheet subrutine in the TimeAndAttendance sheet module, if I place your code in the same sheet module, will it inferfer the the oher code?
    Thank you Mr. Aud for your help again.

  8. #8
    New Lounger
    Join Date
    Mar 2015
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Zeddy,
    Nice work done from your part, too! Let me play with your file and I'll comment if I come across any issues.
    Again, thank you very much for your time and sharing your knowledge.

  9. #9
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Very Nice Zeddy. My first thought was to use conditional formatting to change the interior color of the cells according to code but your method proves to be much simpler.

    Che,
    In column C and D you have the formatting set as "Time 1:30 PM". In those columns, if you were to add the date as well, still only the times would display but column E would no longer have the pound signs

    Example:
    C9: 2/1/2015 3:30 PM
    C9 formatted as "Time 1:30 PM"
    C9 display as 3:30 PM

    D9: 2/2/2015 1:00 AM
    D9 formatted as "Time 1:30 PM"
    D9 display as 1:00 AM

    =D9-C9-TIME(0,F9,0)+TIME(0,10,0) will result as 9:10

    Che2.png

    HTH,
    Maud

  10. #10
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Che,
    To answer your other question, if your macro is not in the Private Sub Worksheet_Change(ByVal Target As Range) event subroutine, there should be no interference between the two provided they are not trying to achieve the same effect. If they are both labeled with the Private Sub Worksheet_Change(ByVal Target As Range) header then they need to be integrated.

    BTW to go from time back to decimal, use the formula =HOUR(E9)+(MINUTE(E9)/60)
    9:10 = 9.17

    Maud

  11. #11
    Star Lounger
    Join Date
    May 2013
    Posts
    59
    Thanks
    34
    Thanked 6 Times in 6 Posts
    Maudibe,

    I think this will do the calculation if the end time is in the next day (after 12MN) without having to add the date. Place this formula in E9 and copy down assuming that col E is formatted to h:mm as you stated

    =IF(D9<C9,TIME(12,0,0)-((C9-D9)-TIME(12,0,0))-TIME(0,F9,0)+TIME(0,10,0),D9-C9-TIME(0,F9,0)+TIME(0,10,0))

    Alexandra

  12. The Following User Says Thank You to Alouso For This Useful Post:

    Maudibe (2015-03-11)

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,827
    Thanks
    136
    Thanked 482 Times in 459 Posts
    Hi

    ..in my file, this part of the formula was essentially
    IF((D9<C9),(D9+1-C9)*24,(D9-C9)*24)-(F9-10)/60)
    ..but instead of coding specifically for 10 minutes (which gives a decimal hour value of 0.17), I used a cell ref $E$6 to have the donning/doffing time specified, i.e.
    IF((D9<C9),(D9+1-C9)*24,(D9-C9)*24)-(F9-$E$6)/60)

    zeddy

  14. #13
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    If you want to preserve the formulas, please see my offering. For the rest, why use a separate file when One file should do it.
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  15. #14
    3 Star Lounger Supershoe's Avatar
    Join Date
    Apr 2014
    Location
    Austin, TX
    Posts
    252
    Thanks
    1
    Thanked 36 Times in 34 Posts
    Use this revised version to copy this worksheet to the master as I assume you are copying many different files.
    Attached Files Attached Files
    Don Guillett
    Excel Developer
    dguillett @gmail.com

  16. #15
    New Lounger
    Join Date
    Mar 2015
    Posts
    15
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I finally got a chance to start testing your files, and I can tell you so far that this is an excellent work from everyone.
    Zeddy, one thing I noticed with your rz-shop floor time and attendance-v1.xlsm file, the Master Shop Floor file can be updated as many time as a user press the ckick here to Update Master Shop Floor file with this data.. buttom which means data can be duplicated and create an enormous discrepancy in hours worked.
    Another thing that I noticed in "updateCel" cell, the date and time remains the same (Thu 12/03/2015 08:47 pm) even if run it at this time or any time.
    I would like to be able to update the Master Shop Floor file one time for the first shift production and one time for the second shift production.
    By the way, very nicelly done with the spoken message!

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •