| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date formula where month and day remain the same, but year will va
I have 3 date fields, (Excel 2003)
The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula where month and day remain the same, but year will va
See if this works, where A1 is Start Date, and B1 is Valuation Date:
=DATE(IF(DATE(YEAR(B1),MONTH(A1),DAY(A1))<B1,YEAR( B1)+1,YEAR(B1)),MONTH(A1),DAY(A1)) HTH, Elkar "Ann" wrote: I have 3 date fields, (Excel 2003) The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula where month and day remain the same, but year wil
I'm sorry, Elkar, when I use your formula, I receive 1900 for the year
value. Any other suggestions? Thanks for the help! "Elkar" wrote: See if this works, where A1 is Start Date, and B1 is Valuation Date: =DATE(IF(DATE(YEAR(B1),MONTH(A1),DAY(A1))<B1,YEAR( B1)+1,YEAR(B1)),MONTH(A1),DAY(A1)) HTH, Elkar "Ann" wrote: I have 3 date fields, (Excel 2003) The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula where month and day remain the same, but year will va
Are you saying you want the day and month from the start date but the year
from the valuation date? If so use =DATE(YEAR(Valuation_Date),MONTH(Start_Date),DAY(S tart_Date)) will return 06/01/08 Instead of posting formulas that don't work describe in words what dates you want using a few different scenarios like this 06/01/06 and the valuation date is 12/15/09 do you want 06/01/09 (which you will get using the formula I provided) or something else? -- Regards, Peo Sjoblom "Ann" wrote in message ... I have 3 date fields, (Excel 2003) The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula where month and day remain the same, but year wil
Thank you, Peo, for the suggestion.
In the scenario you described, the next anniversary date would need to be 6/1/2010. The start date is the first day of the policy, and the valuation date would be a day chosen by the user to take a "snapshot" of the policy's values. The next anniversary date will be the next time the policy's calculations would occur, after the valuation date. Does this help? "Peo Sjoblom" wrote: Are you saying you want the day and month from the start date but the year from the valuation date? If so use =DATE(YEAR(Valuation_Date),MONTH(Start_Date),DAY(S tart_Date)) will return 06/01/08 Instead of posting formulas that don't work describe in words what dates you want using a few different scenarios like this 06/01/06 and the valuation date is 12/15/09 do you want 06/01/09 (which you will get using the formula I provided) or something else? -- Regards, Peo Sjoblom "Ann" wrote in message ... I have 3 date fields, (Excel 2003) The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula where month and day remain the same, but year wil
Does this formula do what you want?
=DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)),MONTH(A1),DAY (A1)) If so, you will need to determine whether to use the less than symbol (<) between the two MONTH evaluations when the valuation month/year is the same as the policy start date's month/year, as shown in my formula, or whether to use the less than or equal (<=) symbol instead. Rick "Ann" wrote in message ... Thank you, Peo, for the suggestion. In the scenario you described, the next anniversary date would need to be 6/1/2010. The start date is the first day of the policy, and the valuation date would be a day chosen by the user to take a "snapshot" of the policy's values. The next anniversary date will be the next time the policy's calculations would occur, after the valuation date. Does this help? "Peo Sjoblom" wrote: Are you saying you want the day and month from the start date but the year from the valuation date? If so use =DATE(YEAR(Valuation_Date),MONTH(Start_Date),DAY(S tart_Date)) will return 06/01/08 Instead of posting formulas that don't work describe in words what dates you want using a few different scenarios like this 06/01/06 and the valuation date is 12/15/09 do you want 06/01/09 (which you will get using the formula I provided) or something else? -- Regards, Peo Sjoblom "Ann" wrote in message ... I have 3 date fields, (Excel 2003) The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula where month and day remain the same, but year wil
Thank you, Rick
Your formula worked for me. "Rick Rothstein (MVP - VB)" wrote: Does this formula do what you want? =DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)),MONTH(A1),DAY (A1)) If so, you will need to determine whether to use the less than symbol (<) between the two MONTH evaluations when the valuation month/year is the same as the policy start date's month/year, as shown in my formula, or whether to use the less than or equal (<=) symbol instead. Rick "Ann" wrote in message ... Thank you, Peo, for the suggestion. In the scenario you described, the next anniversary date would need to be 6/1/2010. The start date is the first day of the policy, and the valuation date would be a day chosen by the user to take a "snapshot" of the policy's values. The next anniversary date will be the next time the policy's calculations would occur, after the valuation date. Does this help? "Peo Sjoblom" wrote: Are you saying you want the day and month from the start date but the year from the valuation date? If so use =DATE(YEAR(Valuation_Date),MONTH(Start_Date),DAY(S tart_Date)) will return 06/01/08 Instead of posting formulas that don't work describe in words what dates you want using a few different scenarios like this 06/01/06 and the valuation date is 12/15/09 do you want 06/01/09 (which you will get using the formula I provided) or something else? -- Regards, Peo Sjoblom "Ann" wrote in message ... I have 3 date fields, (Excel 2003) The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date formula where month and day remain the same, but year wil
But as this formula takes no account of the day of B1 relative to A1 it can
still return a date earlier than B1 (or more than a year after B1 if you change < to <=) Elkar's formula works for me (although you might want to replace < with <=) , or perhaps, using EDATE =EDATE(A1,DATEDIF(A1,B1,"Y")*12+12) "Ann" wrote: Thank you, Rick Your formula worked for me. "Rick Rothstein (MVP - VB)" wrote: Does this formula do what you want? =DATE(YEAR(B1)+(MONTH(A1)<MONTH(B1)),MONTH(A1),DAY (A1)) If so, you will need to determine whether to use the less than symbol (<) between the two MONTH evaluations when the valuation month/year is the same as the policy start date's month/year, as shown in my formula, or whether to use the less than or equal (<=) symbol instead. Rick "Ann" wrote in message ... Thank you, Peo, for the suggestion. In the scenario you described, the next anniversary date would need to be 6/1/2010. The start date is the first day of the policy, and the valuation date would be a day chosen by the user to take a "snapshot" of the policy's values. The next anniversary date will be the next time the policy's calculations would occur, after the valuation date. Does this help? "Peo Sjoblom" wrote: Are you saying you want the day and month from the start date but the year from the valuation date? If so use =DATE(YEAR(Valuation_Date),MONTH(Start_Date),DAY(S tart_Date)) will return 06/01/08 Instead of posting formulas that don't work describe in words what dates you want using a few different scenarios like this 06/01/06 and the valuation date is 12/15/09 do you want 06/01/09 (which you will get using the formula I provided) or something else? -- Regards, Peo Sjoblom "Ann" wrote in message ... I have 3 date fields, (Excel 2003) The Start date, (i.e. 6/1/2006). This field doesn't change once selected. The Valuation date, (i.e. 4/21/2008) This field is a variable. and the Next Anniversary date, where in this example, the formula should return the value, 6/1/2008. For the Next Anniversary date, I have tried, =EDATE(valuation date,(ABS(YEAR(Start date)-YEAR(Valuation Date)) But I get 6/21/2008 returned. I've also tried =DATE(YEAR(valuation date)+1),MONTH(Start Date),DAY(Start Date) And I get 6/1/2009 returned, a bit too far in the future. How do I write the formula to return the nearest Policy Anniversary date, which will always be the same month and day as the start date? Thanks to all. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Date -Month/day/year | Excel Discussion (Misc queries) | |||
| Sort month/date/year data using month and date only | Excel Discussion (Misc queries) | |||
| Using '=MIN(DATE(YEAR(A1),MONTH(A1)+{7,6},DAY(A1)*{0,1}))' | Excel Worksheet Functions | |||
| I need date format to be set up as month/year. | New Users to Excel | |||
| create a date from year, day and month | Excel Worksheet Functions | |||
Hybrid Mode
