Payroll Duties

[UdayVaiyata]

Hello Experts,

I'm working as a HR executive for payroll dept. I have attached a sheet while contents the format of attendance.

This work almost takes 4 to 5 hours because of manual work. I need a solution so I can save my time.

In first sheet all raw data is maintained and in second sheet the final data is prepared.

Pls. check the example given in FINAL SHEET.


Regards,
Uday Vaiyata

[unhappyCamper]

I assume you want col K in Final Data to auto create notes of dates for each class (CL, SL, etc...). Is there a max possible value of 1 for each value CL, SL, etc? If it could be greater than 1 a solution would be significantly more complex.

[UdayVaiyata]

The numbers can be different everytime as it depends on leaves taken by an employee.

[kvsrinivasamurthy]

Is the leave taken for one month are for entire period in Raw data sheet. Because total working days shown is 31.

[Ed_Collins]

So, to clarify, your "Final Data" sheet, as indicated, is not complete, correct? (You've only shown the desired result for just ONE employee, yes?)

Normally when a "Final Data" sheet is presented, it shows the complete desired output for the data given.

And to clarify, you're looking for a solution that will automatically fill in columns G, H, I, J, and K, correct? (You didn't specify exactly what you wanted, other than to "save time" so again, I'm not sure.)

Question... where does the data in Column C, Total Days, come from? The user fills that in?

It sounds like all you need is a macro/VBA code consisting of a double FOR NEXT loop. (Or DO UNTIL loop if that's your preference.) The inner loop looks at each employee number in Column A of the Raw Data sheet. It accumulates the number of CLs, SLs, and LWPs for this particular employee. It loops through every row until the end. Once it's done it writes the data to the current row in the Final Data sheet.

The outer loop, of course, is looping through the employee numbers in the Final Data Column.

Is this an acceptable form of output for column K:

CL taken on 1/1/2013;
SL taken on 6/1/2013;

(No word "and" and semicolons at the end of each date.)

[UdayVaiyata]

Hi Ed and K V Srinivasamurthy

1. Attendance will for 1/1/2013 to 31/1/2013.
2. Data in column C, D, E, F are already given by our boss.
3. I need to just work out in column G, H, I, J, K.

The following answer will be ok

Is this an acceptable form of output for column K:

CL taken on 1/1/2013;
SL taken on 6/1/2013;

(No word "and" and semicolons at the end of each date.)

[kvsrinivasamurthy]

Pl see the attached file.

[UdayVaiyata]

Thanks Mr. Srinivas for the answer

but is it possible that all dates can come in just one column it would be a great help!

[Ed_Collins]

Here's my take on the problem.

Click the UPDATE button when you want the table updated.

Also verify that your formula in the TOTAL DAYS column in column L does want cell j4 SUBTRACTED from the other cells. (That's what your original spreadsheet indicated. =D4+E4+F4+G4+H4+I4+-J4 It's possible that minus sign is just a typo.)

payroll data - updated.xlsm

[UdayVaiyata]

Ed you Rock !!!!

You made my day...... Thanks a lot for the help

The sheet is perfect.