Adding/subtracting dates and the '#NUM!" remark

[BlueskiesChicago]

I need to add or subtract a time period (2 months, for example) to or from a date in another cell. If the other cell is blank, I receive a "1900" date in the new cell when adding; and a "NUM!" remark when subtracting.
If my formula for B17 is
=DATE(YEAR(B39),MONTH(B39)-2,DAY(B39))
and B39 is as yet blank, is there a way to hide the "NUM!" remark until B39 has some information entered?

Thanks for any assistance!

[Aladin Akyurek]

On 2002-04-26 12:52, BlueskiesChicago wrote:
I need to add or subtract a time period (2 months, for example) to or from a date in another cell. If the other cell is blank, I receive a "1900" date in the new cell when adding; and a "NUM!" remark when subtracting.
If my formula for B17 is
=DATE(YEAR(B39),MONTH(B39)-2,DAY(B39))
and B39 is as yet blank, is there a way to hide the "NUM!" remark until B39 has some information entered?

Thanks for any assistance!

Try:

=IF(B39,DATE(YEAR(B39),MONTH(B39)-2,DAY(B39)),"")

[Mark O'Brien]

You could use something like this:

=IF(ERROR.TYPE(DATE(YEAR(B39),MONTH(B39)-2,DAY(B39))) = 6, "No Date Yet", DATE(YEAR(B39),MONTH(B39)-2,DAY(B39)))

but Aladin or maybe even Chris Davison will give a more concise formula.

c.f. http://www.mrexcel.com/board/viewtop...5023&forum=2&4

EDIT:: See? Told you, he even hit Submit just before me, whatta guy.
_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-04-26 12:58 ]

[BlueskiesChicago]

That was fast! Thanks - worked like a charm. Thanks again!