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

Hi Friends,


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

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

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

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

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

How to calculate in excel upto 180?

Correct leave calculation as on 31^st Dec 2012 =  180

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



Shanu

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

How about
=max(opening balance+entitlement-taken,180)
Regards
Harry

Sorry, brain in neutral in last one.
How about
=min(opening balance+entitlement-taken,180)
Regards
Harry

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

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

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

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

Hello Shanu,
Try this.
Leave_mod_a.zip
Regards
Harry

Thanks a lot sir ji..

This is seems good..



Shanu

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.

Hi Ross,

How to do this for next year?

Shanu