EDATE to display "na"

[theexceldunce]

Hello could anyone tell me the correct formula to the below?

My current formula is =EDATE(J165,L165) which returns a #VALUE! error

J165 has the text "na" so I would like this also printed in the cell with the EDATE formula (n165)

Thanks in advance

 

[Aladin Akyurek]

Hello could anyone tell me the correct formula to the below?

My current formula is =EDATE(J165,L165) which returns a #VALUE! error

J165 has the text "na" so I would like this also printed in the cell with the EDATE formula (n165)

Thanks in advance

Try something like...

=IF(N(J165),EDATE(J165,L165),"NA")

 

[T. Valko]

Hello could anyone tell me the correct formula to the below?

My current formula is =EDATE(J165,L165) which returns a #VALUE! error

J165 has the text "na" so I would like this also printed in the cell with the EDATE formula (n165)

Thanks in advance

One way...

=IF(COUNT(J165,L165)=2,EDATE(J165,L165),"NA")

 

[theexceldunce]

Thanks guys I tried out both and both did the trick.

 

[Aladin Akyurek]

Thanks guys I tried out both and both did the trick.

Thanks for providing feedback. I for one appreciate that.

 

[T. Valko]

Thanks guys I tried out both and both did the trick.

You're welcome. Thanks for the feedback!

 

[theexceldunce]

Hello again,
Is it possible to adapt this formula to change the entire row red if the date is 1 month over the current date?

=IF(COUNT(J2,L2)=2,EDATE(J2,L2),"NA")

 

[T. Valko]

Hello again,
Is it possible to adapt this formula to change the entire row red if the date is 1 month over the current date?

=IF(COUNT(J2,L2)=2,EDATE(J2,L2),"NA")

if the date

Which date?

The result of: EDATE(J2,L2) ?

Or, the date in J2 ?

1 month over the current date

A month is not a standard unit of measure. A month can contain anywhere from 28 to 31 days.

If the date in question was 1/31/2011, what date would be "1 month over" ?

What range of cells do you want to format? "The entire row" is kind of ambiguous.

What version of Excel are you using?

 

[theexceldunce]

Hello again,

Thanks for your speedy reply, hope this makes it a bit easier to understand what I am trying to acheive.

i1 Jan-10 (user input mmm-yy)
j1 Jan-10 (user input mmm-yy)
k1 12 (this number is generated by different formula)
l1 24 (this number is generated by different formula)
m1 Jan-11 (generted by formula) =IF(COUNT(I2,K2)=2,EDATE(I2,K2),"NA")
n1 Jan-12 (generated by formula) =IF(COUNT(J2,L2)=2,EDATE(J2,L2),"NA")

The numbers in K1 & L1 are added to the months in I1 & J1 so if I changed K1 to 13 the then M1 would change to Feb-11.

So if the month and year (in M1 & N1) has changed by one month or more above the current date then I would like cells A1:R1 to be highlighted red.

I am using 2007

Many thanks

 

[theexceldunce]

oops just amended error. couldn't find an edit button

So if the month and year (in M1 & N1) has changed by one month or more *below* the current date then I would like cells A1:R1 to be highlighted red.