Fixed the month in a formula with EOMONTH function.

[Prasenjit Basumatary]

Hi,

I am trying to write a formula with EOMONTH function, e.g. =IF($K$6>EOMONTH(G6,0)+"30",(YEAR($K$6)-YEAR(G6))*12+MONTH($K$6)-MONTH(G6)+1,0). I want the formula to act only when cell G6 is end of month March plus 30 days (i.e. 30-04-2014). How do I do that. Please help.

Thanks in advance.

Regards

 

[Somendra Misra]

Hi,

Put in G6 1/3/2014.

Regards,

 

[Prasenjit Basumatary]

Hi,

Put in G6 1/3/2014.

Regards,

Actually there are many dates from different months in column G. The formula should act only when the month is March plus 30 days. Sample workbook attached. The formula column is Q in the sample workbook.

 

[Somendra Misra]

You can try below formula:

=IF(AND($K$6>EOMONTH(G6,0)+"30",MONTH(G6)=3),(YEAR($K$6)-YEAR(G6))*12+MONTH($K$6)-MONTH(G6)+1,0)

Regards,

 

[Prasenjit Basumatary]

You can try below formula:

=IF(AND($K$6>EOMONTH(G6,0)+"30",MONTH(G6)=3),(YEAR($K$6)-YEAR(G6))*12+MONTH($K$6)-MONTH(G6)+1,0)

Regards,

Yes, it works. Thank you for the help. Just make me understand "999999" in the formula. What is this ?
Regards,

 

[Somendra Misra]

Try revised formula in comment # 4.

999999 basically is a big number from which value of K6 will be smaller thus making the condition false.

Regards,

 

[Prasenjit Basumatary]

Yes, the revised formula is also working. This formula is also easier for me to understand. Thank you again for your help and support.
Regards,

 

[Somendra Misra]

Welcome back anytime!!!

Regards,