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

 

[baidya91]

Don't take it otherwise sir, may be I am wrong. I will try the way you want me to check. Thanks once again.

 

[baidya91]

Sir (Mr. Tetra201), will you please find a little time to see what disturbs in working of the formula in case of the date of joining as 25/01/2001 (25 January, 2001)? I have changed the formula as instructed by you: =IF(MONTH(EOMONTH(C12-1,0))<7,EOMONTH(C12,(6+12*10)-MONTH(C12))+(6+12*10),EDATE(C12,12*10)) where C12 is 25/01/2001 but the result I am getting is 03/11/2011 as the date of 10 years' increment in place of 01/07/2011 (1 JUly, 2011). In case of any mistake I beg of your apology in advance.

 

[Tetra201]

Here are the formulas:

for 1 year =IF(MONTH(EOMONTH(A1-1,0))<7,EOMONTH(A1,18-MONTH(A1))+1,EDATE(A1,12))
for 10 years =IF(MONTH(EOMONTH(A1-1,0))<7,EOMONTH(A1,126-MONTH(A1))+1,EDATE(A1,120))
for 20 years =IF(MONTH(EOMONTH(A1-1,0))<7,EOMONTH(A1,246-MONTH(A1))+1,EDATE(A1,240))
for 30 years =IF(MONTH(EOMONTH(A1-1,0))<7,EOMONTH(A1,366-MONTH(A1))+1,EDATE(A1,360))

For 25/01/2001 as the date of joining, the return 01/07/2002, 01/07/2011, 01/07/2021, and 01/07/2031, respectively.

 

[baidya91]

Ok, the formula works fine. Thank you.

 

[Tetra201]

You are welcome.