Formula in excel using date criteria

[Pempa]

I'm trying to formulate a cell where the criteria is as follows;

=IF(A1+365+365-1)<01/01/07,"Asset Fully Depreciated","??/??/??")

A1 = Acquisition date...
and (A1+365+365-1 would arrive at 31Dec06 - implying a 2yr life for an asset)

So what I'm trying to say is that is the asset has expired after it's 2yr
life, then I want it to say "Asset fully depreciated", otherwise, I want it
give me the date at which 2 years expires.
Am I asking too much from excel?

Thanks,

 

[Teethless mama]

=IF(A1+365+365-1)<--"01/01/07","Asset Fully Depreciated","??/??/??")

 

[Ron Rosenfeld]

I'm trying to formulate a cell where the criteria is as follows;

=IF(A1+365+365-1)<01/01/07,"Asset Fully Depreciated","??/??/??")

A1 = Acquisition date...
and (A1+365+365-1 would arrive at 31Dec06 - implying a 2yr life for an asset)

So what I'm trying to say is that is the asset has expired after it's 2yr
life, then I want it to say "Asset fully depreciated", otherwise, I want it
give me the date at which 2 years expires.
Am I asking too much from excel?

Thanks,

No, you're just making a few common errors.

First of all, to express a date:

You write 01/01/07 in a formula. Excel will translate that as
= 1/1/7 --> 0.142857143

If you want Excel to interpret that string as a date, then you must enclose it
in quotes "01/01/07" or, the least ambiguous method would be:

=DATE(2007,1,1)

You could also enter the date in some other cell, and refer to that cell.

Second, if you want to find the date that is two years after a date in A1, you
are probably aware that a year may have either 365 or 366 days in it.

What is 2 years after February 1, 2004? Your formula gives January 30, 2006
where you probably want Jan 31, 2006.

One way of doing that would be to use the EDATE function:

=EDATE(A1,24)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins.
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK.
If necessary, follow the instructions in the setup program.


Finally, depending on how precise you want to be, you may need to be taking
into account the half-year, mid-quarter or mid-month conventions.


--ron