Adding # of Months to calculate a Date

smalik

Board Regular
Joined
Oct 26, 2006
Messages
186
Office Version
  1. 365
Platform
  1. Windows
Is there a way to add # of months to a date and then find out what would be the exact date?

For example if cell A1 = 04/30/07 and Cell B1 = 14 what is the statement that would give me 06/30/08 in Cell C1?

Also, how can I modify the statement if Cell B1 = 14.5 to get 07/15/08?

Any help is greatly appreciated.
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
Forumla in C1

Code:
=DATE(YEAR(A1), MONTH(A1)+B1, DAY(A1))

not sure about .5s might have to convert it to days
 
Upvote 0
Hi zzjasonzz:

You are right. However, Analysis ToolPak Add-in comes with EXCEL.

One can ensure that using Tools|Add-ins ... that box for Analysis ToolPak is checked.
 
Upvote 0
Hi Shabir:

Anotherway would be to use the EDATE function ...

=EDATE(A1,B1)

I believe you need a special add-on installed for this which is why i didn't mention it

Be alert (the world needs more lerts :devilish: ) to the fact that the two formulas will give two different answers to this (an others as well):

1 month after Jan 31, 2007

The first formula gives 3/3/2007
The second formula gives 2/28/2007

While my own personal sense is that the second one is correct, the more important point is that you had better expect this when you ask questions like x months after a certain date - given that a month has 4 different definitions.
 
Upvote 0
Upvote 0
Since you called me eloquent - and I am a firm believer in thinking that no compliment should go unpunished - allow me to continue!

The Feb Mar issue is not the only time we see the problem crop up. The date that Yogi picked is instructive. It is the last day of the month. By the exact same reasoning that edate gives Feb 29, it should also give May 31 (not the 30th). To my mind, edate is inconsistent in every month that has 31 days. (Since it gave the end of month "correctly" for Feb)
 
Upvote 0

Forum statistics

Threads
1,222,017
Messages
6,163,398
Members
451,835
Latest member
kristianb63

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top