Microsoft Excel - Please resolve my queries it’s about leave management database in excel.

Asked By Shanu Singh on 17-Jan-13 05:56 AM


Hi Friends,


Query is a how to laps one third leave in employees leave balance in excel?

Opening Balance EL(1st Jan-2012)                 =  160

This Year EL Entitlement (1st Jan To 31st Dec-2012) =  33

Approved this year EL (1st Jan To 31st Dec-2012)    =  5

Manual calculation  (1st Jan To 31st Dec-2012)      188


How to calculate in excel upto 180?

Correct leave calculation as on 31st Dec 2012 =  180

Note:- In a leave calendar year not more than 180 a employee.



Shanu

Donald Ross replied to Shanu Singh on 17-Jan-13 11:02 AM
This may be more than you are looking for but a simple way to track based on monthly earnings and projected used leave
but this is very basic and hope it helps you develop your own sheet. 

Don

leave.zip

Harry Boughen replied to Shanu Singh on 17-Jan-13 03:07 PM
How about
=max(opening balance+entitlement-taken,180)
Regards
Harry
Harry Boughen replied to Shanu Singh on 17-Jan-13 03:10 PM

Sorry, brain in neutral in last one.
How about
=min(opening balance+entitlement-taken,180)
Regards
Harry
Shanu Singh replied to Harry Boughen on 17-Jan-13 11:51 PM
Hi,

This formula fine working but there is missing a condition of laps 1 third leave in current year entitlement (33/3=11)
Example.
if Opening balance is = 60
Entitlement               =  33
Approved Leave = 5 (one third laps 33/3=11). There should be added 22 only.
This balance after 1 third laps =60+33-11=82.

Example-2

if Opening balance is = 60
Entitlement               =  33
Approved Leave = 11 (one third laps 33/3=11).
This balance after 1 third laps =60+33-11=82.

pls see the attached file.Leave Master.D.Valid.1.9.2012. conf.zip


Shanu





Shanu Singh replied to Donald Ross on 18-Jan-13 12:40 AM

Dear Sir,

Thanks

This is not clear for me.

Actually I have all formatting in a excel sheet.. 
I am looking for formula for calculation opening balance..

pls see the attached file.Leave Master.D.Valid.1.9.2012. conf.zip

Shanu
Harry Boughen replied to Shanu Singh on 18-Jan-13 02:58 AM
Hello Shanu,
I assume that what you want is as well as deducting leave taken, you also wish to deduct one third of the annual entitlement.  However you do not seem to do that in the examples you give below and the manual calculation results in your spreadsheet do not seem to follow that scheme either.
 Is there some other condition that has to be met?
I have modified your sheet to deduct the one third as well as take into account the leave taken but as I said the answers do not correspond with your manual calculation results.
Leave_mod.zip
Hope this gets you on your way.
Harry
Shanu Singh replied to Harry Boughen on 18-Jan-13 05:34 AM

Hello Sir,

Yes, you are right manual calculation not seems correct.

There should be met other condition.

Example -1 from my spreadsheet 

Employee code   :- A2

Employee Name :- BB

His Leave record in a year

 Opening Balance    =    10

Annual entitlement =   33

Approved Leave     =     03

There should be manual calculation (10+33-3-8=32) (Approved leave include in one third deduction)    

Example -2 from my spreadsheet

Employee code :- A12

Employee record :- MS  

His Leave record in a year

 Opening Balance    =    66

Annual entitlement =   33

Approved Leave     =     15

There should be manual calculation (66+33-15=84)

There should not deduct from entitlement because he has taken more than 11.

Shanu

Harry Boughen replied to Shanu Singh on 18-Jan-13 06:20 AM
Hello Shanu,
Try this.
Leave_mod_a.zip
Regards
Harry
Shanu Singh replied to Harry Boughen on 18-Jan-13 06:49 AM

Thanks a lot sir ji..

This is seems good..



Shanu
Donald Ross replied to Shanu Singh on 20-Jan-13 10:49 AM
Sir,

how do you track employees with more than 180?  do they lose it at the end of the year? Use or lose leave.  Or do they sell those days back to teh company.  if your carry forward is greater than 147 and your annual earning is 33 then your projected balance will be greater than 180 and would need to be used prior to the end of the physical year, but you are not doing that on your sheet.

Shanu Singh replied to Donald Ross on 13-Mar-13 08:17 AM
Hi Ross,

How to do this for next year?

Shanu