• 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.

Automatic Leave Balance Working

W

webmax

Member
Hi,


I have attached below the sample worksheet of Attendance Sheets & Leave Balance Sheet


https://www.dropbox.com/s/pxjpapduimf7sap/LEAVE%20BALANCE%20CALCULATION%20WORKINGS.xlsx?m


Following point need to consider before calcuating the Leave Balance


1) If the PL Leave Balance is Zero or Availed More than the Leave Balance adjust the Leave in SL Balance same for SL & CL Vice versa.

For Example: if the PL Leave Balance is 5 SL Leave Balance is 5 and CL Leave Balance is 5. If the Employee taken 7 Days in PL Leave first adjust 5 Days in PL and Balance in SL.


2) If there is no Leave Balance in PL , SL & CL Automatically marked as LOP


For Example: if the PL Leave Balance is 1 SL Leave Balance is 1 and CL Leave Balance is 1. If the Employee taken 7 Days in PL Leave first adjust 1 Day in PL, 1 Day in SL and 1 Day in SL and balance 4 Days marked as LOP.


3) In case of LOP is marked in Attendance Sheet it should be appear in LOP directly.


4) In case of PL Leave Taken from Friday to Tuesday Adjust the Leave as 4 Days. (It Should Consider Saturday & Sunday). This condition will apply for only PL.
 
Hi webmax,


I downloaded the file and it says "format not supported" kindly look into it!


Regards,
 
Hi Faseeh,


Download is working fine for me..

Re-Download or download this one..

https://dl.dropboxusercontent.com/u/78831150/Excel/LEAVE%20BALANCE%20CALCULATION%20WORKINGS.xlsx


Please go-ahead.. :)


Regards,

Deb
 
Hi webmax,


please check this file, confirm the results and reply the comments:


https://dl.dropboxusercontent.com/u/60644346/LEAVE%20BALANCE%20CALCULATION%20WORKINGS.xlsx


Regards,
 
hi faseeh,


You have taken the Leave availed from the Total Leave Availed for example PL+SL+CL.


But i want this leave availed to be split in two three columns like PL, SL & CL

If the PL Balance is 5 the PL Leave Availed is 3 Balance PL Balance to be 2

If the SL Balance is 1 the SL Leave Availed is 2 The Excess 1 Leave availed of SL to be adjust against PL

IF the CL Balance is 3 the CL Leave Availed is 10. The Excess 7 Leave availed to be adjust against PL & SL and there is no leave balance the same to be reflect in LOP.
 
The solved file is posted in this link

https://docs.google.com/file/d/0B7MlbNwyiUNHRGtwalpmY3NNV3c/edit?usp=sharing
 
Hi Webmax,


Sorry for replying late, i am busy in my exams. You can directly download the file from dropbox or srinidhi's link.


Well i worked out from the filling from left to right, if that is incorrect?? I started utilizing PL that was to the extreme left, if that was fully utilized then to remaining two in the same manner and remaining to the final column to right,


I suggest you to download the file and please recheck.


Regards,
 
Hi Webmax,


Please see this file and comment on Adjusted Leaves:


https://dl.dropboxusercontent.com/u/60644346/LEAVE%20BALANCE%20CALCULATION%20WORKINGS%20-%202.xlsx


Regards & Good Night!

Faseeh
 
You must log in or register to reply here.
Back
Top