auto filling of months

[ajitsingh]

hi there can anyone help me with this problem, i have attached the excel file and written the problem in it.

it is like this a person earns 5 days leave for every completed month of duty and he accumulates 30 days in half yearly basis, month is to be counted only if first date is earlier than 15 of that particular month i.e if a person joins on 10 feb it will be counted but if he joins on 16 or later date that month wont be counted. now what i am trying is:
a. if i enter date in from column the to column should automatically take last date of that part of half year i.e. in above eg it should be 30 jun.
b. the remaining rows after that date should fill dates yearwise.

 

[shg]

May I respectfully suggest that you read your post and wonder if someone could reasonably understand your question?

 

[Peter_SSs]

And since you cannot attach a file, you could consider using one of the 3 methods in my signature block to post a small screen shot to help illustrate your description.

 

[ajitsingh]

Excel Workbook
	A	B	C	D	E	F	G	H	I
2							example leave availed
3	Year	Period From	Period To	Period in months	Leave Earned	leave credit	Lve From	Lve To	No of Day leave availed
4		20-Jul-01	31-Dec-01	5	25	25			15
5		01-Jan-02	This column should have 30 jun 02	this is what I tried IF(M5>15, P5-N5,(P5-N5)+1)	#VALUE!	#VALUE!			0
6		Here it should be 01 jul 02	here 31 dec 02	#VALUE!	#VALUE!	#VALUE!	14-Sep-02	18-Oct-02	35
7		here 01 jan 03	and so on	#VALUE!	#VALUE!	#VALUE!			0
8				1	5	#REF!	19-Jul-03	16-Sep-03	60
9				1	5	#REF!	10-May-04	08-Jul-04	60
10				1	5	#REF!	03-Dec-04	22-Dec-04	20
11				1	5	#REF!	24-May-05	22-Jul-05	60
12				1	5	#REF!			0
13				1	5	#REF!	30-Jan-06	30-Mar-06	60
14				1	5	#REF!			0
15				1	5	#REF!	03-Feb-07	03-Apr-07	60
16				1	5	#REF!			0
17				1	5	#REF!	18-Jan-08	04-Feb-08	18
18				1	5	#REF!	15-May-08	13-Jul-08	60
19				1	5	#REF!	29-Jan-09	29-Mar-09	60
20				1	5	#REF!			0
21				1	5	#REF!	16-Jan-10	01-Mar-10	45
22				1	5	#REF!			0
23				1	5	#REF!	10-Jan-11	10-Mar-11	60
24
25		The concept is a person earns 5 days leaves on completing 15 days in a month and if leave is unavailed it is accumulated half yearly i.e. 30 per 6 months
26		in case a person avails more than 30 days in first half it is deducted from earlier accumulated leave.
27		therefore I want once I enter first date remaining half yearly dates should come automatically upto current year half
28
Sheet1

Excel 2010

Cell Formulas
Range	Formula
D4	=IF(K4>15, N4-L4,(N4-L4)+1)
D6	=IF(K6>15, N6-L6,(N6-L6)+1)
D7	=IF(K7>15, N7-L7,(N7-L7)+1)
D8	=IF(K8>15, N8-L8,(N8-L8)+1)
D9	=IF(K9>15, N9-L9,(N9-L9)+1)
D10	=IF(K10>15, N10-L10,(N10-L10)+1)
D11	=IF(K11>15, N11-L11,(N11-L11)+1)
D12	=IF(K12>15, N12-L12,(N12-L12)+1)
D13	=IF(K13>15, N13-L13,(N13-L13)+1)
D14	=IF(K14>15, N14-L14,(N14-L14)+1)
D15	=IF(K15>15, N15-L15,(N15-L15)+1)
D16	=IF(K16>15, N16-L16,(N16-L16)+1)
D17	=IF(K17>15, N17-L17,(N17-L17)+1)
D18	=IF(K18>15, N18-L18,(N18-L18)+1)
D19	=IF(K19>15, N19-L19,(N19-L19)+1)
D20	=IF(K20>15, N20-L20,(N20-L20)+1)
D21	=IF(K21>15, N21-L21,(N21-L21)+1)
D22	=IF(K22>15, N22-L22,(N22-L22)+1)
D23	=IF(K23>15, N23-L23,(N23-L23)+1)
E4	=5*D4
E5	=5*D5
E6	=5*D6
E7	=5*D7
E8	=5*D8
E9	=5*D9
E10	=5*D10
E11	=5*D11
E12	=5*D12
E13	=5*D13
E14	=5*D14
E15	=5*D15
E16	=5*D16
E17	=5*D17
E18	=5*D18
E19	=5*D19
E20	=5*D20
E21	=5*D21
E22	=5*D22
E23	=5*D23
F4	=E4
F5	=IF(E5+#REF!<=300,E5+#REF!,300)
F6	=IF(E6+#REF!<=300,E6+#REF!,300)
F7	=IF(E7+#REF!<=300,E7+#REF!,300)
F8	=IF(E8+#REF!<=300,E8+#REF!,300)
F9	=IF(E9+#REF!<=300,E9+#REF!,300)
F10	=IF(E10+#REF!<=300,E10+#REF!,300)
F11	=IF(E11+#REF!<=300,E11+#REF!,300)
F12	=IF(E12+#REF!<=300,E12+#REF!,300)
F13	=IF(E13+#REF!<=300,E13+#REF!,300)
F14	=IF(E14+#REF!<=300,E14+#REF!,300)
F15	=IF(E15+#REF!<=300,E15+#REF!,300)
F16	=IF(E16+#REF!<=300,E16+#REF!,300)
F17	=IF(E17+#REF!<=300,E17+#REF!,300)
F18	=IF(E18+#REF!<=300,E18+#REF!,300)
F19	=IF(E19+#REF!<=300,E19+#REF!,300)
F20	=IF(E20+#REF!<=300,E20+#REF!,300)
F21	=IF(E21+#REF!<=300,E21+#REF!,300)
F22	=IF(E22+#REF!<=300,E22+#REF!,300)
F23	=IF(E23+#REF!<=300,E23+#REF!,300)
I5	=IF(G5=0,0,(H5-G5)+1)
I6	=IF(G6=0,0,(H6-G6)+1)
I7	=IF(G7=0,0,(H7-G7)+1)
I8	=IF(G8=0,0,(H8-G8)+1)
I9	=IF(G9=0,0,(H9-G9)+1)
I10	=IF(G10=0,0,(H10-G10)+1)
I11	=IF(G11=0,0,(H11-G11)+1)
I12	=IF(G12=0,0,(H12-G12)+1)
I13	=IF(G13=0,0,(H13-G13)+1)
I14	=IF(G14=0,0,(H14-G14)+1)
I15	=IF(G15=0,0,(H15-G15)+1)
I16	=IF(G16=0,0,(H16-G16)+1)
I17	=IF(G17=0,0,(H17-G17)+1)
I18	=IF(G18=0,0,(H18-G18)+1)
I19	=IF(G19=0,0,(H19-G19)+1)
I20	=IF(G20=0,0,(H20-G20)+1)
I21	=IF(G21=0,0,(H21-G21)+1)
I22	=IF(G22=0,0,(H22-G22)+1)
I23	=IF(G23=0,0,(H23-G23)+1)

 

[Peter_SSs]

Firstly, when using the HTML Maker, investigate the options for not displaying all the worksheet formulas. This long list of formulas, many of which are the same just fills up the board and makes your post more difficult to navigate. It is possible to show none or just a few of the formulas from your screen shot so I suggest that you have a play in the Test Here forum to learn how to do that.

From what I can gather from reading your posts, you want formulas to fill the 'half year' dates in columns B:C once one date has been filled in. If that is correct then try these formulas (copied down). If this is not what you want, post back with more details.

Excel Workbook
	B	C
3	Period From	Period To
4	20-Jul-01	31-Dec-01
5	01-Jan-02	30-Jun-02
6	01-Jul-02	31-Dec-02
7	01-Jan-03	30-Jun-03
8	01-Jul-03	31-Dec-03
9	01-Jan-04	30-Jun-04
Fill Dates

 

[ajitsingh]

thanx a lot yes this exactly what i wanted. u guy's are great.