Date and formulas

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(DA TE(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added months

-----Original Message-----
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
.

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(YE AR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

-----Original Message-----
Hi
use the formula:
=DATE(YEAR(A1),MONTH(A1)+months,MIN(DAY(A1),DAY(D ATE(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added months

-----Original Message-----
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
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(Y EAR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

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

-----Original Message-----
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
.

.

.

No, It's a UK version.

Greg

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

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

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

-----Original Message-----
No, It's a UK version.

Greg

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

-----Original Message-----
Hi
use the formula:
=DATE(YEAR(A1),MONTH(A1)+months,MIN(DAY(A1),DA Y(DATE
(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added months

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

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

-----Original Message-----
No, It's a UK version.

Greg

-----Original Message-----
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(DAT E(YEAR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

-----Original Message-----
Hi
use the formula:
=DATE(YEAR(A1),MONTH(A1)+months,MIN(DAY(A1),D AY(DATE
(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added months

-----Original Message-----
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'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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


wrote in message
...
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

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

-----Original Message-----
No, It's a UK version.

Greg

-----Original Message-----
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(DAT E(YEAR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

-----Original Message-----
Hi
use the formula:
=DATE(YEAR(A1),MONTH(A1)+months,MIN(DAY(A1),D AY(DATE
(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added months

-----Original Message-----
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
.

.

.

.

.

.

Excellent, thank you both for your help.

Fred's formualas work just how I want them.

Thanks.

Greg

-----Original Message-----
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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


wrote in message
...
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

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

-----Original Message-----
No, It's a UK version.

Greg

-----Original Message-----
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(DAT E(YEAR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

-----Original Message-----
Hi
use the formula:
=DATE(YEAR(A1),MONTH(A1)+months,MIN(DAY(A1),D AY
(DATE
(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added
months

-----Original Message-----
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 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.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia

wrote in message
...
Excellent, thank you both for your help.

Fred's formualas work just how I want them.

Thanks.

Greg

-----Original Message-----
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.

--
Regards,
Fred
Please reply to newsgroup, not e-mail


wrote in message
...
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

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

-----Original Message-----
No, It's a UK version.

Greg

-----Original Message-----
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(DAT E(YEAR
(A1),MONTH(A1)+6+1,0))))

Am I doing something else wrong?

Thanks
Greg

-----Original Message-----
Hi
use the formula:
=DATE(YEAR(A1),MONTH(A1)+months,MIN(DAY(A1),D AY
(DATE
(YEAR
(A1),MONTH(A1)+months+1,0))))

Simply replace months with your number of added
months

-----Original Message-----
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
.

.

.

.

.

.



.