Increment Date Calculation in excel

[baidya91]

I want a formula in excel to calculate date of increment of salary with the following conditions:

If date of joining falls between 2nd January to 30th June, date of increment will be 1st July next year but if date of joining falls between 1st July to 1st January, date of increment will be the date of joining next year

For example, if the date of joining be 25 January, 2001, date of increment will be 1 July, 2002 but if the date joining be 7 August, 2008, date of increment will be 7 August, 2009. I want to calculate the date of increment automatically in excel with the conditions mentioned above. Can anybody help me with the problem? Thanks

Date of joining Date of increment

25.01.2001 01.07.2002

03.03.2011 01.07.2012

07.08.2008 07.08.2009

11.11.1999 11.11.2000

 

[Dave Patton]

Excel 2010
	A	B	C	D	E	F
1	25-Jan-01	1-Jul-02
2	3-Mar-11	1-Jul-12
3	7-Aug-08	7-Aug-09
4	11-Nov-99	11-Nov-00
5
6
7		What do you want to show if start date is Jan 1?
8
4d

Cell Formulas
Range	Formula
B1	=AND(A1>DATE(YEAR(A1),1,1),A1<=DATE(YEAR(A1),6,30))*DATE(YEAR(A1)+1,7,1)+AND(A1>DATE(YEAR(A1),6,30),A1<=DATE(YEAR(A1),12,31))*DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))

 

[baidya91]

If the date of joining is 01.01.2001, the date of increment will be 01.01.2002

 

[Tetra201]

Try this:

=IF(MONTH(EOMONTH(A1-1,0))<7,EOMONTH(A1,18-MONTH(A1))+1,EDATE(A1,12))

 

[baidya91]

Mr. Dave Patton, the formula does not apply in case of 01/01/2001 as a date of joining. Please do something.

 

[Dave Patton]

Tetra201's formula looks much better.

Baidya91 stated
"Mr. Dave Patton, the formula does not apply in case of 01/01/2001 as a date of joining. Please do something."

to Baidya91
This is your situation. I asked for clarification on 01-01-yyyy.
You could expand the formula.
Tetra201's solution addresses that anomaly.

 

[baidya91]

Mr. Patton you are right that Mr. Tetra201's formula looks much better but this formula does not apply in case of 10/20/30 years' increment. It is applicable only in the case of annual increment. So I request you to revise your formula to sort out the anomaly of 01/01/2001.

 

[baidya91]

Sorry Sir (Mr. Patton), Tetra201's formula is working fine in case of 20/30/10 years' increment. I made a mistake in altering the figure in the formula. Thanks both of you. Don't mind, Mr. Tetra201. You are right from the beginning. Thanks for the help provided to me.

 

[baidya91]

Dear Mr. Tetra201, will you suggest a way how calculate the date of 10/20/30 years' increment if the date of joining 25/01/2001 or 24/01/2003? I tried to alter the last figure of your formula 12 (=IF(MONTH(EOMONTH(A1-1,0))<7,EOMONTH(A1,18-MONTH(A1))+1,EDATE(A1,12))) into 120, 240 or 360 to suit my purpose of 10/20/30 years' increment but it does not work in case of joining date 25/01/2001 but works fine in case of joining date 06/07/1990. Please help me.

 

[Tetra201]

Sorry Sir (Mr. Patton), Tetra201's formula is working fine in case of 20/30/10 years' increment. I made a mistake in altering the figure in the formula. Thanks both of you. Don't mind, Mr. Tetra201. You are right from the beginning. Thanks for the help provided to me.

Dear Mr. Tetra201, will you suggest a way how calculate the date of 10/20/30 years' increment if the date of joining 25/01/2001 or 24/01/2003? I tried to alter the last figure of your formula 12 (=IF(MONTH(EOMONTH(A1-1,0))<7,EOMONTH(A1,18-MONTH(A1))+1,EDATE(A1,12))) into 120, 240 or 360 to suit my purpose of 10/20/30 years' increment but it does not work in case of joining date 25/01/2001 but works fine in case of joining date 06/07/1990. Please help me.

Here is how to adjust my formula:

18 --> 6+12*n; 12 --> 12*n, where n=1,2,3,...,10,...

I also encourage you to triple-check your calculations before saying that somebody's formula does not work.