Surya prakash
-
Jul 21, 2011 at 01:18 AM
rizvisa1
Posts4478Registration dateThursday January 28, 2010StatusContributorLast seenMay 5, 2022
-
Jul 28, 2011 at 01:17 PM
Dear All,
Can anyone please assist me in composing a macro to capture the break timing of my team members. Am desperately in need of this macro to capture their break timing as this would reflect in my appraisal.
It would be helpful and greatful if anyone could assist me on this.
I have listed down the points on how the macro should run.
1) I need a macro to capture the start time and end time of the employee.
2) I would have two command buttons, one with start time and other with End time.
3) When I click on the Start time command button, it should prompt for a input box to enter the employee number. When I enter the employee number, it should go the particular cell where the employee number is available and should capture the start time in the cell which has the start time field. (Same opertaion should be performed for end time command button also)
4) An employee should be able to capture the start time and end time only once in a day. He should not be able to re-enter it. If anyone wants to change it, then it should be only if the person knows the password. (This is because to avoid the employee entering the duplicate shift timing)
5) The start time should be entered based on the system date. For example if am present in the office today and am going for a break now and if I clcik on the start time button, It should capture the time in the cell with the current date. This macros should not allow the employees to capture time for back dated or for future dated. (backdated and future dated action can be performed only if the person know the password)
6) As soon as the time is captured by the employee spreadsheet should be saved automatically and it should not allow them to enter the time once again. (for eg: if I click on start time and it is captured in the cell which has current date, spreadsheet whould be saved automatically and if I click on the start time again it should not allow me to capture the start time again)
7) When the start time and end time is captured. Both the time should be subtrated and the difference of minutes should be captured in the time taken cell. (This is to monitor the minutes the employee has taken break)
8) A single spreadsheet will be maintained for a particular month. (for example: I would maintain a single spreadsheet for themonth of august). [this would be as (cell c will have start time, D will have end time, E will capture the time taken minutes-> this will be for the Date August 1st].
Again for August 2nd. Start time will be in cell F, end time in Cell G, time taken in cell H. Same the way it will be for the other dates too.
Please assist me in preparing the macto with the above criterias. This is really going to help me in my promotion and appraisal. I would be thankful to you if you can look into this at the earliest and provide me with the solution.
I have updated the template of my spreadsheet below.
Employee# Employee
Name 20-07-2011 21-07-2011
StartTim EndTime Time taken Start Time End Time Timetaken
12345 Raj
34567 Ajay
34567 Vijay
98765 Lal
67845 Mani
34213 Andrew
67809 Vinay
44356 Suraj
66654 Swetha
96975 Suji
I riz. thank you for the response. I checked the link & the macro isworking fine.
I have few things that needs to be updated from that macro.
1) When I run the macro it is capturing the time correct. But it is considering the last empty cell. For example: In my Macro Start time is updated in cell C and end time in cell D, cell E will capture the minutes of time taken by employee (i.e. cell C - cell D). If I update the formula in cell E, time is not captured in cell C & cell D. Macro is considering the empty cell next to the cell which has a value. can u please modify this particular code for me.
2) In this macro that start time and time time has the same code. But in my request, there will be two command buttons (START TIME & END TIME). When I click on start time it should capture the time in the cell which has a start time. When I click on end time it should capture the time in the cell which has a end time.
3) When the time is captured spreadsheet should be automatically saved and reenter should not be done. If the employee tries to change the time it should prompt for a password to be entered. (Spreadsheet password will be chosen by me)
4) The time should be captured only in the cell which has today's date. For example: we will consider the date July 25th. The data will be as [Start Time in Cell C] --> [End Time in Cell D] --> [break timing taken by employee in Cell E]. I will merge all these three columns and the Date july 25th will be updated. If today is july 25, the macro should capture the time only in the cells which has today's date. Macro should not allow the user to capture time in the previous dates & future dates columns.
5) If an employee is on leave on a particular date itshould capture as leave in those cells. (for example: if the employee is leave on july 24 & when we open the spreadsheet on july 25, the cells should be filled as leave on the cells which is blank).
Please help me Riz. This macro is a key for my promotion. I need to submit it as cristal clear as I can.
Waiting for ur answer
rizvisa1
Posts4478Registration dateThursday January 28, 2010StatusContributorLast seenMay 5, 2022766 Jul 26, 2011 at 08:05 AM
for #1., please read the whole thread to which I pointed you to.
for #2, you can modify the sub to pass a parameter to indicate if it is start time or end time and update the appropriate column accordingly
for #3, again read the thread,
for #4, from what I see, you are saying that start time is in C and end time is in D, where is date in the picture ?
for #5, how one would say that employee is on leave
Jul 24, 2011 at 01:33 PM
I have few things that needs to be updated from that macro.
1) When I run the macro it is capturing the time correct. But it is considering the last empty cell. For example: In my Macro Start time is updated in cell C and end time in cell D, cell E will capture the minutes of time taken by employee (i.e. cell C - cell D). If I update the formula in cell E, time is not captured in cell C & cell D. Macro is considering the empty cell next to the cell which has a value. can u please modify this particular code for me.
2) In this macro that start time and time time has the same code. But in my request, there will be two command buttons (START TIME & END TIME). When I click on start time it should capture the time in the cell which has a start time. When I click on end time it should capture the time in the cell which has a end time.
3) When the time is captured spreadsheet should be automatically saved and reenter should not be done. If the employee tries to change the time it should prompt for a password to be entered. (Spreadsheet password will be chosen by me)
4) The time should be captured only in the cell which has today's date. For example: we will consider the date July 25th. The data will be as [Start Time in Cell C] --> [End Time in Cell D] --> [break timing taken by employee in Cell E]. I will merge all these three columns and the Date july 25th will be updated. If today is july 25, the macro should capture the time only in the cells which has today's date. Macro should not allow the user to capture time in the previous dates & future dates columns.
5) If an employee is on leave on a particular date itshould capture as leave in those cells. (for example: if the employee is leave on july 24 & when we open the spreadsheet on july 25, the cells should be filled as leave on the cells which is blank).
Please help me Riz. This macro is a key for my promotion. I need to submit it as cristal clear as I can.
Waiting for ur answer
Jul 26, 2011 at 08:05 AM
for #2, you can modify the sub to pass a parameter to indicate if it is start time or end time and update the appropriate column accordingly
for #3, again read the thread,
for #4, from what I see, you are saying that start time is in C and end time is in D, where is date in the picture ?
for #5, how one would say that employee is on leave
Jul 28, 2011 at 11:39 AM
Jul 28, 2011 at 01:17 PM