Add months and adding partial monhts
 

Hi -

When I need to add a month, to a date. I use -

=DATE(YEAR(B27),MONTH(B27)+S21,MIN(DAY(B27),DAY(DA TE(YEAR(B27),MONTH(B27)+S22,0))))

However, I sometimes need to enter 1.5 months or 2.5. I figured out a way
to do this by looking at the number and figuring out if it is a decimal then
adding the decimal to the days -

=DATE(YEAR(N26),MONTH(N26),DAY(N26)+(DAY(P26)*L26) )

My question is, how do I do this with one formula. It seems like a took the
long road to this solution.

Any suggestions?

Adam

Answer: Add months and adding partial monhts
 

Hi Adam,

Great question! There is actually a simpler way to add partial months to a date in Excel. You can use the EDATE function, which allows you to add a specified number of months to a date, including partial months.

Here's the formula you can use:

1. =EDATE(B27,S21)

In this formula, B27 is the date you want to add months to, and S21 is the number of months you want to add. You can enter a decimal value for S21 to add partial months.

For example, if you want to add 1.5 months to the date in cell B27, you can use the formula:

1. =EDATE(B27,1.5)

This will add one month and 15 days to the date in cell B27.

If you can resolve what you mean by half a month, then just add that number of
days. Month length is between 28 and 31. If you decide half a month is 15
days, you should be able to include that in the DAY part of your formula.

On Wed, 27 Oct 2004 09:23:07 -0700, Adam C <Adam
wrote:

Hi -

When I need to add a month, to a date. I use -

=DATE(YEAR(B27),MONTH(B27)+S21,MIN(DAY(B27),DAY(D ATE(YEAR(B27),MONTH(B27)+S22,0))))

However, I sometimes need to enter 1.5 months or 2.5. I figured out a way
to do this by looking at the number and figuring out if it is a decimal then
adding the decimal to the days -

=DATE(YEAR(N26),MONTH(N26),DAY(N26)+(DAY(P26)*L26 ))

My question is, how do I do this with one formula. It seems like a took the
long road to this solution.

Any suggestions?

Adam

On Wed, 27 Oct 2004 09:23:07 -0700, Adam C <Adam
wrote:

Hi -

When I need to add a month, to a date. I use -

=DATE(YEAR(B27),MONTH(B27)+S21,MIN(DAY(B27),DAY(D ATE(YEAR(B27),MONTH(B27)+S22,0))))

However, I sometimes need to enter 1.5 months or 2.5. I figured out a way
to do this by looking at the number and figuring out if it is a decimal then
adding the decimal to the days -

=DATE(YEAR(N26),MONTH(N26),DAY(N26)+(DAY(P26)*L26 ))

My question is, how do I do this with one formula. It seems like a took the
long road to this solution.

Any suggestions?

Adam


Perhaps something like:

=MIN(DATE(YEAR(A1),MONTH(A1)+A2,DAY(A1)+MOD(A2,1)* 30),
DATE(YEAR(A1),MONTH(A1)+A2+1,0+MOD(A2,1)*30))

or, if you have the analysis tool pak installed:

=edate(A1,A2)+MOD(A2,1)*30


--ron