Adding # of Months to calculate a Date

smalik

Board Regular
Joined
Oct 26, 2006
Messages
154
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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
Forumla in C1

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

not sure about .5s might have to convert it to days
 

smalik

Board Regular
Joined
Oct 26, 2006
Messages
154
Office Version
  1. 365
Platform
  1. Windows
Thanks! This is great. I works.
 

zzjasonzz

Well-known Member
Joined
Apr 23, 2006
Messages
649
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
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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.
 

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

mortgageman

Well-known Member
Joined
Jun 30, 2005
Messages
2,015
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)
 

Forum statistics

Threads
1,181,055
Messages
5,927,858
Members
436,573
Latest member
CMR237

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
Top