Microsoft Excel - How can get 0 value by formula after completion 58 year in EPS data?

Dear All,

How to get 0 value or other message as like (Retirement) completion 58 year's from DOB to retirement date in next month?

Example:-

DOB

Retirement date of EPS

Age

No.of Days

Paid days

Basic

EPS  Gross

EPS Deduction p.m

25-Feb-55      25-Feb-55     58                 28      28      70260          6500         541

1- there should show 0 or message(Retirement) in eps gross column. This condition should allow completion after 58 years retirement coming in next month as on 01.03.2026 show 0 value by formula.
 2- Should not show 0 in retirement month (From 1-Feb-2013 to 28-Feb-2013).



Thanks
Shanu

Hi Shanu,
The following formula will work for most cases but will need some work around for somebody who has a birthday in December.
=IF(AND((TODAY()-A5)/365.25>58,MONTH(TODAY())>MONTH(A5)),"retired",F5/12)
Regards
Harry
EDIT: Sorry Shanu this only works in very limited cases.  needs some more work.
Harry
EDIT:
I think this goes closer.
=IF(OR((TODAY()-A5)/365.25>58.1,AND((TODAY()-A5)/365.25>58,MONTH(TODAY())>MONTH(A5))),"retired",F5/12)
Harry

Hi Harry,

This formula not seems closer. please see the attached file.. you can try in this excel on column EPS gross.

Note:- There is already working an formula. you can merge formula in same column..
Should not break any formula, you can merge formula.

Also
You can modify formula as you need..


Shanu

sorry there is going somthing wrong so i am not geting upload option.
  

shanu.zip

Hello Shanu,
This is a file with the formula in G5.  You will have to change the calculation for the Gross if the person has not retired.
Regards
Harry

Thanks 

Dear Harry,
when I am using the your formula, I am losing the eps gross value.

Now attached file please see this attachment file.. 

For EPS.zip


Shanu

Hi Shanu,
Put this into K2 and copy it down.

=IF(OR((TODAY()-E2)/365.25>58.1,AND((TODAY()-E2)/365.25>58,MONTH(TODAY())>MONTH(E2))),0,IF(J2>6499,6500,IF(J2<6500,J2,))/H2*I2)

Regards
Harry

Harry,

Thanks a lot.

This is seems ok working as condition.

There is a condition.
If eps retirement date is 01-Feb-2013 there should show eps gross value from 01-Feb-2013 to 28-Feb-2013 yet (end of the current month).
As need The retirement value should show 0 from 01-March-2013 to onward.


EPS Gross should not show 0 value within retirement month. The Value should consider 0 in coming month.

Is it possible or not?

Shanu
   
 

Hello Shanu,
I don 't understand your problem,  I have tested the formula and for a retirement date on 1 Feb (ie a DOB 2 Feb 2026) the answer is 6500 right through until 28 Feb and then on 1 Mar it changes to zero.
Perhaps if you can be a bit more specific or repost the file with the error highlighted I might be able to help.
Regards
Harry

Hi Harry,

Theis formula not working as you said in last reply..

=IF(OR((TODAY()-E2)/365.25>58.1,AND((TODAY()-E2)/365.25>58,MONTH(TODAY())>MONTH(E2))),0,IF(J2>6499,6500,IF(J2<6500,J2,))/H2*I2)

I was trying  on DOB 02-Feb-1955. but there was getting zero from 1-feb-2013.
See blow query.

DOB	Retirement date of EPS	Age	No.of Days	Paid days	Basic	EPS Gross	EPS
2-Feb-55	1-Feb-13	58.00	28	28	555555	0	0

putting this formula in EPS Gross column.
=IF(OR((TODAY()-E2)/365.25>58.1,AND((TODAY()-E2)/365.25>58,MONTH(TODAY())>MONTH(E2))),0,IF(J2>6499,6500,IF(J2<6500,J2,))/H2*I2)


Shanu
   

Hello Shanu,
Because this formula uses todays date (TODAY()), it considers that the retirement month for someone born in February 1955 (and earlier) is finished and so the value has to be zero.  If you want to compare to some other date to determine the age then you need to put that date into a cell somewhere and replace the TODAY() parts of the formula with references to the date cell.
So if the reference date cell is, say, N2 then the formula would become

=IF(OR(($N$2-E2)/365.25>58.1,AND(($N$2-E2)/365.25>58,MONTH($N$2)>MONTH(E2))),0,IF(J2>6499,6500,IF(J2<6500,J2,))/H2*I2)

Regards
Harry

Thanks for trying  Harry


Shanu