Help in Date and Year

[AVG123]

Dear Friends I have a excel sheet which full of date

(1) 24.09.2000 = it should be 01.05.2001 {Date 24 should be 01} {Month 09 show to 05} and {Year 2000 always increase one more 2001)

(2) 03.05.1999 = it should be 01.05.2000 {Date 03 should be 01} {Month 05 show to 05} and {Year 1999 show as 2000}

(3) 28.04.2005 = it should be 01.05.2005 {Date 28 should be 01} {Month 04 show to 05} and {Year 2005 show as 2005}

(4) 15.03.2001 = it should be 01-05-2002 {Date15 should be 01} {Month 03 show to 05} and {Year 2001 show as 2001}


Conditions:-

Days=Always show 01

Month= If month less than 5, then Month shows 05, and year should be intact (1999)

Month= If month greater than 5, then Month shows 05, and year should be increase one (2000)
for example:- 01.03.1999= it should be 01.05.1999
01.05.1999= it should be 01.05.2000

please friends help me please..........

[:) Sixthsense :)]

	a	b
1	24.09.2000	01/05/2001
2	03.05.1999	01/05/2000
3	28.04.2005	01/05/2005
4	15.03.2001	01/05/2001

	b
1	=date(if(month(value(substitute(a1,".","-")))<5,year(value(substitute(a1,".","-"))),year(value(substitute(a1,".","-")))+1),5,1)
2	=date(if(month(value(substitute(a2,".","-")))<5,year(value(substitute(a2,".","-"))),year(value(substitute(a2,".","-")))+1),5,1)
3	=date(if(month(value(substitute(a3,".","-")))<5,year(value(substitute(a3,".","-"))),year(value(substitute(a3,".","-")))+1),5,1)
4	=date(if(month(value(substitute(a4,".","-")))<5,year(value(substitute(a4,".","-"))),year(value(substitute(a4,".","-")))+1),5,1)

[AVG123]

Sixthsense
Sir you solve my query......your formula works like a charm........
and please do me a favor can you please explain this formula........its my humbly request to you.............

[:) Sixthsense :)]

Use Evaluate Formulas to know how the formula works.

Refer the below link to know how to do it

http://www.excel-user.com/2010/10/ev...xcel-2010.html