Date and formulas

[Guest]

Hi

I have a spreadsheet with a load of dates when a
particular service was carried out.

I would like to create a formula in another cell with a
date which is exactly 6 months, and in another cell, 12
months after the original date.

Is this possible?

Thanks for any help
Greg

 

[Frank Kabel]

Hi
use the formula:
=DATE(YEAR(A1),MONTH(A1)+months,MIN(DAY(A1),DAY(DATE(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added months

 

[Guest]

Thank you for the very quick answer.

I have copied the formula and changed it but am getting
#NAME? come up.

I presume A1 is the cell where the original date is?
If so changing months to 6 gives the formala like this:

=DATE(YEAR(A1),MONTH(A1)+6,MIN(DAY(A1),DAY(DATE(YEAR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

 

[Frank Kabel]

Hi
are you using a non-English excel version?

-----Original Message-----
Thank you for the very quick answer.

I have copied the formula and changed it but am getting
#NAME? come up.

I presume A1 is the cell where the original date is?
If so changing months to 6 gives the formala like this:

=DATE(YEAR(A1),MONTH(A1)+6,MIN(DAY(A1),DAY(DATE(YEAR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

.

 

[Guest]

No, It's a UK version.

Greg

-----Original Message-----
Hi
are you using a non-English excel version?

.

 

[Frank Kabel]

Hi
this shouldn't matter
Does the formula parser show you an individual formula
element which went wrong.
You may also try to enter the formulas
=DAY(A1)
=MONTH(A1)
=YEAR(A1)

and see if they work. You may also check that the formula
you posted is the EXACT formula you have used

 

[Guest]

Hi
Thanks for your continuing help.

The formula I posted is the exact one I am using as I
copied and pasted it from excel.

The date is for example 04/05/04, when I enter Day(A1) I
get 4, MONTH(A1) I get 5 and YEAR(A1) I get 2004. So this
works OK.

When I entered the forumla I got no message come up all it
shows is the #NAME? What is the forumla parser?

Thanks
Greg

 

[Fred Smith]

Frank's formula covers all the bases, because it maps Aug 29, 30 and 31 to
the end of February. To track down the error, try some simpler formulas:

Exactly 12 months from a date is: =date(year(a1)+1,month(a1),day(a1))
For 6 months from a date, try: =date(year(a1),month(a1)+6,day(a1))

Fred.

 

[Guest]

Excellent, thank you both for your help.

Fred's formualas work just how I want them.

Thanks.

Greg

 

[Norman Harker]

Hi Greg!

Have you tested the 6 month addition formula against A1 as 31-Aug-2004

It returns 3-Mar-2005

That is what Frank is addressing and what Fred has pointed out.