Calculation months from date

Please read comment in sample spreadsheet
Who is Participating?
barry houdiniConnect With a Mentor Commented:
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
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

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
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.