hi

I am not getting your last condition!!

Please explain, why it should be extended to December 31st?

Thanks

Hi, Vibhav,

Thanks for replying me....

that is the special condition in our company only.

Pl give suitable formula.

hi

so yo mean if employee DOB between Jan to April then superannuation date should be 1 days before.

If his DOB is after 1 may then it should be set to 31 Dec like that?

From what i understood.. try this::

=IF(DAY(A1)=1,EDATE(A1,58*12)-1,DATE(YEAR(A1)+58,12,31))

where cell A1 contauns your Date of Birth.

Cheers!!

Hi, Vibhav,

Thanks for response...

not like that sir, if any employee DOB is 1st date of any of month, then his superannuation is jumps to previous month last day. otherwise means from 2nd onwards the said S.A to be extended to 31-Dec-Year.

pl do the needful.

Hi vibhav ji, earlier you have given single line command.........

/forum/retirement-date-formula-226231.asp#.UhIb_dJgf74

pl do the needful.

Originally posted by : sathya
	Hi, Vibhav, Thanks for response... not like that sir, if any employee DOB is 1st date of any of month, then his superannuation is jumps to previous month last day. otherwise means from 2nd onwards the said S.A to be extended to 31-Dec-Year. pl do the needful.

Try this,

=IF(DAY(A1)=1,EDATE(A1,58*12)-1,DATE(YEAR(A1)+58,12,31))   where cell A1 contains your Date of Birth.  

Download File

Cheers!!

THANK YOU VERY MUCH SIR JI.

T H A N K... U... 

Hi, Vibhav ,

small correction required , i.e, when his DOB on 01-Jan-Year means his S.A date should be 1 day before, other all dates & months S.A  should be moved to 31-dec-Year.

I have noticed one error;

if DOB 01-Oct-1956, it is giving 30-09-2014, actually it should be come 31-Dec-2014 as per our requirement.

pl do the needful.

Hi

Check this: =IF(AND(DAY(A1)=1,MONTH(A1)=1),EDATE(A1,58*12)-1,DATE(YEAR(A1)+58,12,31))

Where A1 contains DOB.

Thanks!!

Hi, Vibhav ,

THANKS A LOT

Hi Sathya,

See attached file for another approch without using edate formula, i.e. No need to install Analiysis Toolpack.