# Calculating Dates backward in Excel

Discussion in 'Business Applications' started by rover, Sep 21, 2004.

Not open for further replies.

Joined:
Sep 21, 2004
Messages:
3
Hello,
I need some help with a formula which will allow me to calculate dates in reverse. I have critical dates which are not adjustable (project letting dates) and need to calculate dtae for plans review.
I need to calculate 1 day prior to the let date then
28 days prior to the one day prior date,
then 60 days prior to the above date.
then 90 days prior to the 60 days date.

the formula needs to be day, month and year sensitive.
I have attempted and continue to recieve a circular error message.
To date i can not see where I reference the same cell but i guess I had to have done this.

Any suggestions would be appreciated!

2. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
For one day prior: =a1-1
For 28 days prior: =a1-28

and so on...

Circular references are caused when you put, in a1: =a1-1
You can't calculate ON a cell in the same cell.

3. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
Oh....
The cells that contain Circular references will show on your status bar.

Joined:
Sep 21, 2004
Messages:
3
Dreamboat,
Thanks for the info. Your solution of course works however I failed to mention the true problem which has to do with networkdays. I can not seem to write a formula which will take into account the net work days parameter.

5. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
What's happening?
Show me your formula that's not working.

You DO have the Analysis toolpak turned on under Tools-Addins, yes? It is required to be able to use that function.

6. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
Hi Rover,

Although you can create circular references, it's highly recommended that you don't - unless you have the specific need to do so, which is rare indeed. If you don't have the Analysis Toolpak there are ways around it. But as Dreamboat has stated, post your formula(s) or needs not covered.

Joined:
Sep 21, 2004
Messages:
3
Dreamboat,

I turned on the Add-in function. I did not have them turned on at first and the following formula does not calculate.
=networkdays(A1-31)
What .o

8. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
Hit the fx button for help with the formula.
You'll find that you're using the wrong syntax.

It's =networkdays(startdate,enddate,holidays)

In other words, no minus signs...

As Seen On