[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 247
  • Last Modified:

Calculation months from date

Please read comment in sample spreadsheet
milestone-formula.xls
0
propolis
Asked:
propolis
  • 2
1 Solution
 
Michael FowlerSolutions ConsultantCommented:
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
http://support.microsoft.com/kb/214134

Michael
0
 
barry houdiniCommented:
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
 
propolisAuthor Commented:
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
 
barry houdiniCommented:
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now