Condition based on Date_Nested If

[uday]

Hi,

I need to customize a formula which reflect "6 months" if date is within 6 months calculating from today and beyond 6 months shows message "Over 6 months". However I want extend the criteria little bit more. Like, it would only extract year if any date is older that last year which 2017. i.e., today is 25-06-2018 and + 6 months is 25-01-2018, the text should be reflect 6 months and anything more should be over 6 months. However if the date still in 2017 which is last year the text also needs to be reflect as "over 6 months". Anything beyond 2017 should only reflect the year of the date.

Regards,
Uday

 

[p45cal]

try:

=IF((YEAR(TODAY())-YEAR(A2))>1,YEAR(A2),IF(A2<EDATE(TODAY(),-6),"Over 6 months","6 months"))

 

[Harshad Maloo]

=IF((TODAY()-A2)>365,YEAR(A2),IF((TODAY()-A2)>180,"Over 6 months","6 Months"))

 

[John Jairo V]

Hi, to all!

Could be more accurate this formula:

=CHOOSE(MATCH(TODAY(),EDATE(A2,{0;6;12})),"6 Months","Over 6 Months",YEAR(A2))

Blessings!

 

[p45cal]

John, it doesn't take into account the last 2 sentences of msg#1.

 

[uday]

Thanks everyone for your time and effort-


Can I apply the same logic in the basis of Month instead of Today?

i.e.,current year Jan to June is 6 months and before Jan it should be over 6 months till 2017 Jan, then before 2017 Jan it should only pick year whatever the date is mentioned in target cell.

Regards,
Uday