• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get Leave days and work days of an employee

Status
Not open for further replies.

Dackson

Member
Hi Friend,

This particular employee Name “X” or “Y” in September 2013 took leave below mentioned dates.
He took leave from 2nd Sep 2013 to 12th Sep 2013 continues (Find in cell C5, E5). Also He took leave 22nd Sep 2013 to 24th Sep 2013 continues (Find in cell C6, E6) and he took leave 28nd Sep 2013 to 29th Sep 2013 continues (find in cell C7, E7). This data has been entered in continues leave days field.
He took leave 17th Sep 2013 Single day (Find in cell H5) and 20th Sep 2013 Single day (Find in cell K5). This data has been entered in single leave day field (Find H5: K7).
The 1st Row mentioned that 1-30 days means month days. As per this input data condition reflected in 2nd row (Find in A2 to AD2). When employee will take leave then that particular day (data find cell H5: K7 from anywhere in the single leave day) or days (data find from continues leave days cell C5,E5 ; C6,E6 ; C5,C6 ) will be marked “L” (leave) and background will be blue color other all days will be marked “P”(Present) back ground yellow color in the 2nd row.
Summary Report (Find V4 :AD9)
1. Total Leave days in the cell AA4
2. Leave days mention in sorting method.
1. “FROM 02/09/2013 TO 12/09/2013” in the cell V5 and Cell AD5 total days for the continue section.
2. “17/09/2013” in the cell V6 and Cell AD6 total day for the day.
3. “20/09/2013” in the cell V7 and Cell AD7 total day for the day.
4. “FROM 22/09/2013 TO 24/09/2013” in the cell V8 and Cell AD8 total days for the continue section.
5. “FROM 28/09/2013 TO 29/09/2013” in the cell V9 and Cell AD9 total days for the continue section.
3. Total work days in the cell A12
Attached file and above mentioned data for example of one employee. Suppose I will apply changes in the data field then automatically I want to reflect this changes in 2nd row accordingly leave days (include blue back ground blue color) and present days and Summary report.
While entered in the data field some conditions will be following for avoid duplication and error data.
Conditions below mentioned.
1. Data field: In continues leave days ‘C column < E column’ and not bigger than month last days.
2. Data field: single leave day H5 : K7 not bigger than month last days.
3. In the all data field means Continues and single leave days don’t want to take same day.
Above three points required for error massage through data validation or other method.
Conditions For Summary report
1. Shown Continues leave days in one line and single leave days the next line.
2. Smallest date showing in top then next down.
3. V5 to V9 shown only if they have leave days means reporting row not fixed. If employee has leave 1 day leave then show 1 row in report summary. If more leave accordingly in report summary more rows.

I feel you are understood more than me now.

Thanks and regards
Dackson Jose
 

Attachments

Status
Not open for further replies.
Top