1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Calculating Dates backward in Excel

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

Thread Status:
Not open for further replies.
Advertisement
  1. rover

    rover Thread Starter

    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

    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

    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.
     
  4. rover

    rover Thread Starter

    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

    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

    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. :)
     
  7. rover

    rover Thread Starter

    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

    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...
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/276498

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice