Excel formula

[baidya91]

A kind of leave called commuted leave is credited half the half pay leave (another leave) every year. While commuted leave is credited maximum 30 in a service period, half pay leave is credited 60 in a service period (15 every year). Now if A1 = Half Pay Leave Credit, B1=Commuted Leave Credit and C1=Commuted Leave Availed, which excel formula has to be used to calculate the Commuted Leave Credit in B1? Please help...

https://www.dropbox.com/s/2pmrofd8w7...EAVE.xlsx?dl=0

 

[baidya91]

A kind of leave called commuted leave is credited half the half pay leave (another leave) every year. While commuted leave is credited maximum 30 in a service period, half pay leave is credited 60 in a service period (15 every year). Now if A1 = Half Pay Leave Credit, B1=Commuted Leave Credit and C1=Commuted Leave Availed, which excel formula has to be used to calculate the Commuted Leave Credit in B1? Please help...

https://www.dropbox.com/s/2pmrofd8w7...EAVE.xlsx?dl=0

Please help me
..

 

[baidya91]

Please help me
..

Name of the school
Name of the staff:
			DAY	MONTH	YEAR
		DOB	22	10	1975
		DOJ	25	1	2001
		DOR	31	10	2035
Length of service	Half Pay Leave	Commuted Leave
5	30
From	To	Credited	Enjoyed	Balance	Eligible	Enjoyed
25/01/2002	24/01/2003	15	5	2	1	4
25/01/2003	24/01/2004	15	0	3	1	7
25/01/2004	24/01/2005	15		8	4	5
25/01/2005	24/01/2006	15		3	1	10
25/01/2006	24/01/2007	15		10	5	4		IF I17 = 5, THEN H18 WILL BE -1, IF I17 = 4, THEN H18 WILL BE 0 AND IN BOTH CASES ELIGIBLE COMMUTED WILL NOT BE CREDITED AS MAXIMUM NO OF COMMUTED LEAVE IS 30, THEREFORE CELLS FROM H19 TO H62 WILL BE NIL OR BLANK. PLEASE SUGGEST A SUITABLE EXCEL FORMULA TO DO THAT.
25/01/2007	24/01/2008	15		25	12
25/01/2008	24/01/2009	15		40	20
25/01/2009	24/01/2010	15		55	27
25/01/2010	24/01/2011	15		60	30
25/01/2011	24/01/2012	15		60	30
25/01/2012	24/01/2013	15		60	30
25/01/2013	24/01/2014	15		60	30
25/01/2014	24/01/2015	15		60	30
25/01/2015	24/01/2016	15		60	30
25/01/2016	24/01/2017	15		60	30
25/01/2017	24/01/2018	15		60	30
25/01/2018	24/01/2019	15		60	30
25/01/2019	24/01/2020	15		60	30
25/01/2020	24/01/2021	15		60	30
25/01/2021	24/01/2022	15		60	30
25/01/2022	24/01/2023	15		60	30
25/01/2023	24/01/2024	15		60	30
25/01/2024	24/01/2025	15		60	30
25/01/2025	24/01/2026	15		60	30
25/01/2026	24/01/2027	15		60	30
25/01/2027	24/01/2028	15		60	30
25/01/2028	24/01/2029	15		60	30
25/01/2029	24/01/2030	15		60	30
25/01/2030	24/01/2031	15		60	30
25/01/2031	24/01/2032	15		60	30
25/01/2032	24/01/2033	15		60	30
25/01/2033	24/01/2034	15		60	30
25/01/2034	24/01/2035	15		60	30
25/01/2035	31/10/2035	11		60	30

<colgroup><col><col span="4"><col span="2"><col span="16"></colgroup><tbody>
</tbody>