If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Date and formulas
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 . |
#3
|
|||
|
|||
Date and formulas
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 . . |
#4
|
|||
|
|||
Date and formulas
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 . . . |
#5
|
|||
|
|||
Date and formulas
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 . . . . |
#6
|
|||
|
|||
Date and formulas
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 . . . . . |
#7
|
|||
|
|||
Date and formulas
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 . . . . . . |
#8
|
|||
|
|||
Date and formulas
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 . . . . . . |
#9
|
|||
|
|||
Date and formulas
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 . . . . . . . |
#10
|
|||
|
|||
Date and formulas
|
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Looking for Solution for Excluding Weekends in Date Motivated Formulas | Bob Phillips | Worksheet Functions | 2 | April 9th, 2004 08:53 PM |
Date formulas (date + no of days to return date mon-dri only) | Frank Kabel | Worksheet Functions | 0 | March 5th, 2004 11:27 AM |