Mac 2004 Excel - issue with date formula

[Hope]

I have researched to determine a formula that would produce a date
based on an existing date and then adding a numeric value.

Basically, if the project starts today (8/31/2007), 3 BUSINESS days it
should be completed.

So here is the formula that I am using:
=DATE(YEAR(DATEVALUE(A1))+0,MONTH(DATEVALUE(A1))+0,DAY(DATEVALUE(A1))
+3)

This formula gives me an error. If I use actual dates in the A1
reference in quotations, I get August 3, 2007 as the answer...doesn't
make sense.

The information that I got from the Microsoft site was the following:
How to Increase Dates Incrementally
To increase a date by a number of years, months, or days, use the
formula
=DATE(YEAR(reference)+value1,MONTH(reference)+value2,DAY(reference)
+value3)
where reference is either the date value or cell reference that
contains the date, and value1, value2, and value3 are the increments
by which you want to increase the year, month, and day, respectively.

For example, to increase a date by one month, the formula is:
=DATE(YEAR(DATEVALUE("6/20/96")),MONTH(DATEVALUE("6/20/96"))+1,
DAY(DATEVALUE("6/20/96")))

My problem is that I don't want the date to be static or constant, I
want to reference the date in a cell (which it says you can do). The
other issue is that the VALUE 1,2,3 I also want to reference it in a
cell.

HELP!!!

 

[JE McGimpsey]

I have researched to determine a formula that would produce a date
based on an existing date and then adding a numeric value.

Basically, if the project starts today (8/31/2007), 3 BUSINESS days it
should be completed.

One method of accomplishing this, assuming your business days are Monday
thru Friday:

=WORKDAY(A1,3)

This requires that you load the Analysis Toolpak Add-in
(Tools/Add-ins...). You may also need to format the cell as a date.


I think your error comes from using DATEVALUE(A1) - if A1 is an actual
XL date, rather than a text string than can be converted to a date, it
returns a #VALUE! error.

To make the number of days variable, put it in a cell (say, B1) and use

=WORKDAY(A1, B1)

If you're not worried about skipping weekends, use the much simpler

=A1+B1

formating it as a date (since XL stores dates as integer offsets from a
base date).

 

[Hope]

This is simplier, however, I want the dates to go backwards not
forwards. Basically, the date is an end date, and the numbers are to
be SUBTRACTED from that date. (just realizing this now....my bad).
What I want to accomplish is the end date, and back date all the
events that are required to get completed, in the end, showing a START
DATE.

Your thoughts?
Hope

 

[JE McGimpsey]

Your thoughts?

Look at the entry for WORKDAY() in XL Help...