Date and formulas

  • Thread starter Thread starter Guest
  • Start date Start date
G

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

.
Click to expand...
 
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
 
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
 
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.
 
Excellent, thank you both for your help.

Fred's formualas work just how I want them.

Thanks.

Greg
 
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.
 
Back
Top