Calculation months from date

Posted on 2011-10-12
Last Modified: 2012-05-12
Please read comment in sample spreadsheet
Question by:propolis
    LVL 23

    Expert Comment

    This formula will give you what you are after

    ="contract start date + " &(YEAR(D5)-YEAR($E$1))*12+MONTH(D5)-MONTH($E$1) & " month"

    Just place it in cell B5 and drag it down. Note you will need to remove the formula in D5 or it will cause a circular reference

    For more info on the formula see

    LVL 50

    Expert Comment

    by:barry houdini
    You can use DATEDIF to determine the number of months between two dates, perhaps try this version

    ="contract start date + "&DATEDIF(E$1,D5,"m")&" months"

    regards, barry

    Author Comment

    Hi Michael,

    This seems to be working except for one thing.  I put the formula in Column B, but no date in Column D, then it show a weird display in column B.  Can I hide whats in column B until a valid date has been entered in column D

    LVL 50

    Accepted Solution

    Hello Propolis, did you try my suggestion too?

    For either formula you can use this concept to show a blank when D5 is blank


    so for my suggestion that would become

    =IF(D5="","","contract start date + "&DATEDIF(E$1,D5,"m")&" months")

    regards, barry

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Join & Write a Comment

    Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now