Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Repeating the Same Date Each Year
Hi,
The subject title says it all really...does anyone know how I can use a formula in Excel to repeat the same date each year, allowing for leap years? e.g. if the value in cell C1 is 28/2/07, I want the formula to automatically put 28/2/08 in cell D1. I know I could just use C1+365 but that wouldn't account for the extra day in a leap year. Any suggestions? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Repeating the Same Date Each Year
D1: =DATE(YEAR(C1)+1,MONTH(C1),DAY(C1))
"Dickie Worton" wrote: Hi, The subject title says it all really...does anyone know how I can use a formula in Excel to repeat the same date each year, allowing for leap years? e.g. if the value in cell C1 is 28/2/07, I want the formula to automatically put 28/2/08 in cell D1. I know I could just use C1+365 but that wouldn't account for the extra day in a leap year. Any suggestions? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Repeating the Same Date Each Year
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
With your starting date in A1 enter this in A2 and drag right as far as you want "Dickie Worton" wrote: Hi, The subject title says it all really...does anyone know how I can use a formula in Excel to repeat the same date each year, allowing for leap years? e.g. if the value in cell C1 is 28/2/07, I want the formula to automatically put 28/2/08 in cell D1. I know I could just use C1+365 but that wouldn't account for the extra day in a leap year. Any suggestions? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Repeating the Same Date Each Year
sorry I meant put it in B1 and drag right
"Mike" wrote: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) With your starting date in A1 enter this in A2 and drag right as far as you want "Dickie Worton" wrote: Hi, The subject title says it all really...does anyone know how I can use a formula in Excel to repeat the same date each year, allowing for leap years? e.g. if the value in cell C1 is 28/2/07, I want the formula to automatically put 28/2/08 in cell D1. I know I could just use C1+365 but that wouldn't account for the extra day in a leap year. Any suggestions? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Repeating the Same Date Each Year
Ignore the previous reply. Here is the correct formula
=EOMONTH(C1,12) Required Analysis Toolpak Add Ins "Dickie Worton" wrote: Hi, The subject title says it all really...does anyone know how I can use a formula in Excel to repeat the same date each year, allowing for leap years? e.g. if the value in cell C1 is 28/2/07, I want the formula to automatically put 28/2/08 in cell D1. I know I could just use C1+365 but that wouldn't account for the extra day in a leap year. Any suggestions? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Repeating the Same Date Each Year
Mike / 'Mama',
Thanks both for your responses, took me a moment to work out that EOMONTH stands for end of month, so won't work for dates other than the last day of the month, but both will be useful. Dickie "Teethless mama" wrote: Ignore the previous reply. Here is the correct formula =EOMONTH(C1,12) Required Analysis Toolpak Add Ins "Dickie Worton" wrote: Hi, The subject title says it all really...does anyone know how I can use a formula in Excel to repeat the same date each year, allowing for leap years? e.g. if the value in cell C1 is 28/2/07, I want the formula to automatically put 28/2/08 in cell D1. I know I could just use C1+365 but that wouldn't account for the extra day in a leap year. Any suggestions? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Formula for Repeating the Same Date Each Year
Hi. The usual question is if the date is 2/29/2008, what do you want 1 year
in the future to be? Another option might be: =EDATE(A1,12) -- HTH :) Dana DeLouis Windows XP & Office 2007 "Dickie Worton" wrote in message ... Mike / 'Mama', Thanks both for your responses, took me a moment to work out that EOMONTH stands for end of month, so won't work for dates other than the last day of the month, but both will be useful. Dickie "Teethless mama" wrote: Ignore the previous reply. Here is the correct formula =EOMONTH(C1,12) Required Analysis Toolpak Add Ins "Dickie Worton" wrote: Hi, The subject title says it all really...does anyone know how I can use a formula in Excel to repeat the same date each year, allowing for leap years? e.g. if the value in cell C1 is 28/2/07, I want the formula to automatically put 28/2/08 in cell D1. I know I could just use C1+365 but that wouldn't account for the extra day in a leap year. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help w/formula to add 1 year to cell (date done) date due? | Excel Worksheet Functions | |||
Year-to-date year to date formula | Excel Worksheet Functions | |||
Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
Formula to get a day of the year from a date | Excel Discussion (Misc queries) | |||
dynamic year to date formula | Excel Worksheet Functions |