Solved

# Calculation months from date

Posted on 2011-10-12
194 Views
milestone-formula.xls
0
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

http://support.microsoft.com/kb/214134

Michael
0

LVL 50

Expert Comment

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
0

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

Eddie
0

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

=IF(D5="","",original_formula)

so for my suggestion that would become

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

regards, barry
0

## Featured Post

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 …