PDA

View Full Version : Counting Employee hours booked



Mnet
11-19-2017, 03:37 PM
Dear Excel users - I have a Userform were the employees book their hours work on specific jobs with job numbers and activities. It is working perfectly. Now I want to count the hours and show it to the employee each time he fills in the form e.g he might work .2 hours on job 123 and .6 hours on job 234 and 2 hours on job 345. Each time he activate the sheet to put his firstname, Lastname, jobnumber etc. in for the next job (It only captures one job at a time), it must show him how many hour he have already booked. All the data that is filled in is captures on a second worksheet called "Data". Any suggestions on how I can incorporate this on my Userform? All idees, suggestions or solutions will be highly appreciated.

Bob Phillips
11-19-2017, 07:24 PM
Just do a simple COUNTIF of the job number, e.g.


txtHoursWorked.Text = Application.Countif(Worksheets("TimeData").Columns("D"), txtJobNumber.Text)

adjusted o your situation of course

Mnet
11-20-2017, 01:31 PM
Dear xld there is 45 different jobs and 100 different activities that it links to .....so I need to count a persons hours booked not the job. I have tried it for both and not working. Just to clarify - each job is recorded separately if e.g. John worked on three different jobs, each one is recorded separately. Each time John book a job it must tell him you have already booked 4 hrs and the next one say 6 hours if he work one hour on the next job and so on. Hope this helps. Thank you!!

Bob Phillips
11-21-2017, 02:03 AM
That is exactly what I gave you, but it does need adapting because I have no idea what your data or your form look like.

greyangel
11-21-2017, 12:13 PM
Would you be able to post an example of the workbook. That would allow us to help you with writing some type of VBA that could help you.

Mnet
11-22-2017, 06:04 PM
Please see attachment - Thank You!

greyangel
11-22-2017, 08:05 PM
Would this work? Each time the "Hours worked" box is changed a sumif takes place in the background and matches the person's hours to the date.

Mnet
11-22-2017, 08:51 PM
Dear greyangel - thank you for the great work I have changed it to show on the Data Sheet in P2 - I do not want to Add or Delete another sheet - it must run in the background. I have tested it and it is not working it does not sum it only shows the amount of hours that was put in for hour worked . If e.g. Peter Pan need to fill in 4 timesheets on the same day for 4 different jobs it must sum his total. E.g. If job 123 was 2 hrs and he fills in the next time sheet it was 1 hrs it must show in the counter 3 hrs so that Peter knows he has 5 hrs left for an 8 hour day......does it make sense? Also getting error when sending to Database.....



Private Sub Reg7_change()
'Sheets.Add
Range("P2").Formula = "=SUMIFS(Data!I:I,Data!C:C," & Reg1.Value & ",Data!E:E," & Reg3.Value & ",Data!F:F," & Reg4.Value & ")+ " & Reg7.Value & ""
Label29 = Range("P2").Value
'ActiveSheet.Delete
End Sub

greyangel
11-23-2017, 06:57 AM
Try this one. I can't really run the macro because my excel does not recognize the Format() feature and your spreadsheets are locked. The sumifs function should work as long as the date, Last name, and firstnme box matches whats on the spreadsheet exactly.

Mnet
11-23-2017, 02:12 PM
Dear Greyangel it is working perfectly - Thank you so much your time ,effort and help.... is greatly appreciated!!! :hi: