+ Reply to Thread
Results 1 to 11 of 11

date formula

  1. #1
    Registered User
    Join Date
    09-21-2008
    Location
    Maine
    Posts
    2

    date formula

    Hi,

    I'm new here and am wondering if anyone can help me with a formula to determine a date 9 months from the date in a cell. For example, if I have a date such as 9/20/08 in cell D3, in cell E3 I'd like to enter a formula to automatically calculate the date 9 months from then. I need to have it take the calendar year into consideration, so that it will display 6/20/09, not 6/20 or 6/20/08.

    Thanks in advance for any help you can give.

    Laura

  2. #2
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    Hi and welcome to the board
    Try =DATE(YEAR(A1), MONTH(A1)+9, DAY(A1))
    That is if your starting date is in A1

    HTH

  3. #3
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    function EDATE
    =EDATE(TODAY(),9)
    or =EDATE(A1,9)
    where first value is date, second value is number of months away you want
    i think this requires the analysis tool pak activated,
    go to tools/add-ins and check the analysis tool pak
    Last edited by martindwilson; 09-21-2008 at 01:14 PM.

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673
    Both good, but different results for some dates, e.g., 9 months from 5/30.
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    09-21-2008
    Location
    Maine
    Posts
    2
    Thank you, Arthur and Martin, for your quick responses. I tried both suggestions and Arthur's is working for me. I get a #NAME? with Martin's formula. Perhaps that is because I only have Excel 2002. I appreciate the help with this issue. It's going to save me so much time!

  6. #6
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    yep it looks like edate has a problem with
    29/05/2008
    30/05/2008
    31/05/2008
    all return 28-Feb-09

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,673
    I don't think it's a problem; it could be what you want on one occasion, and the other on another ...

  8. #8
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    it only happens when the month in the future has less days than your start month so i suppose it just returns end of month as its closest within that month.

  9. #9
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    IF you use

    =DATE(YEAR(A1), MONTH(A1)+9, DAY(A1))

    and A1 is 31st May 2008 then you get 3rd March 2009. I'd think that 28th February would normally be more acceptable.

    To get the same result as EDATE without using Analysis ToolPak functions

    =MIN(DATE(YEAR(A1), MONTH(A1)+9+{0,1}, DAY(A1)*{1,0}))

  10. #10
    Valued Forum Contributor rwgrietveld's Avatar
    Join Date
    09-02-2008
    Location
    Netherlands
    MS-Off Ver
    XL 2007 / XL 2010
    Posts
    1,671

    re: date formula

    daddylonglegs,

    You use the zero (0) in DATE(2008;X;0) to give the last day of the previous month. What is the logic behind this. Is it an anomaly of the DATE function, is it meant to be like this or is it something you found by trial & error?

    I'm always interested in the hidden features. Great solution though!
    Looking for great solutions but hate waiting?
    Seach this Forum through Google

    www.Google.com
    (e.g. +multiple +IF site:excelforum.com/excel-general/ )

    www.Google.com
    (e.g. +fill +combobox site:excelforum.com/excel-programming/ )

    Ave,
    Ricardo

  11. #11
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    It might not be common knowledge, but it is described in Excel Help for DATE function.

    Zero or negative numbers can be used for any of the YEAR, MONTH or DAY arguments within DATE function [as long as the resultant date isn't negative in its entirety], e.g.

    =DATE(2008,-1,-1)

    gives 30th October 2007

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. date formula
    By shmeetbag in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2008, 06:16 PM
  2. Replies: 3
    Last Post: 01-18-2008, 09:09 PM
  3. looping issues
    By Marcus Gee in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-09-2007, 11:42 PM
  4. Calendar Formula Problem
    By roadburner in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 10-07-2007, 06:36 PM
  5. Urgent HELP for Formula for a date problem on Excel!
    By flip_360_2002 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-14-2006, 12:14 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1