| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
A Year in advance ?
Hello,
I am using Excel 2007 My problem is this, I am entering dates with a renewal date of a year in advance minus 1 day : A1 B1 30/01/08 29/01/09 20/03/08 19/08/09 01/01/08 31/12/08 01/02/09 31/01/10 and so on ..... Presently I am using Vlookup() to put the information into B1,B2,B3,etc automatically,however the date data is getting increasing long. Is there a formulua that I can enter in B1,B2,etc that will achieve this without resorting to Vlookup() All offers of help greatly accepted, and my thanks to all respondents FinanceGuru |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1
-- David Biddulph "Finance Guru" wrote in message ... Hello, I am using Excel 2007 My problem is this, I am entering dates with a renewal date of a year in advance minus 1 day : A1 B1 30/01/08 29/01/09 20/03/08 19/08/09 01/01/08 31/12/08 01/02/09 31/01/10 and so on ..... Presently I am using Vlookup() to put the information into B1,B2,B3,etc automatically,however the date data is getting increasing long. Is there a formulua that I can enter in B1,B2,etc that will achieve this without resorting to Vlookup() All offers of help greatly accepted, and my thanks to all respondents FinanceGuru |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1
-- Kind regards, Niek Otten Microsoft MVP - Excel "Finance Guru" wrote in message ... | Hello, | I am using Excel 2007 | | My problem is this, I am entering dates with a renewal date of a year in | advance minus 1 day : | | A1 B1 | 30/01/08 29/01/09 | 20/03/08 19/08/09 | 01/01/08 31/12/08 | 01/02/09 31/01/10 | and so on ..... | | Presently I am using Vlookup() to put the information into B1,B2,B3,etc | automatically,however the date data is getting increasing long. | | Is there a formulua that I can enter in B1,B2,etc that will achieve this | without resorting to Vlookup() | | All offers of help greatly accepted, and my thanks to all respondents | FinanceGuru | | |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
David / Niek
Thank you very much. Much appreciated FG "David Biddulph" wrote: =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1 -- David Biddulph "Finance Guru" wrote in message ... Hello, I am using Excel 2007 My problem is this, I am entering dates with a renewal date of a year in advance minus 1 day : A1 B1 30/01/08 29/01/09 20/03/08 19/08/09 01/01/08 31/12/08 01/02/09 31/01/10 and so on ..... Presently I am using Vlookup() to put the information into B1,B2,B3,etc automatically,however the date data is getting increasing long. Is there a formulua that I can enter in B1,B2,etc that will achieve this without resorting to Vlookup() All offers of help greatly accepted, and my thanks to all respondents FinanceGuru |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
David / Niek
Thanks very much for the quick response. Very much appreciated FG "David Biddulph" wrote: =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1 -- David Biddulph "Finance Guru" wrote in message ... Hello, I am using Excel 2007 My problem is this, I am entering dates with a renewal date of a year in advance minus 1 day : A1 B1 30/01/08 29/01/09 20/03/08 19/08/09 01/01/08 31/12/08 01/02/09 31/01/10 and so on ..... Presently I am using Vlookup() to put the information into B1,B2,B3,etc automatically,however the date data is getting increasing long. Is there a formulua that I can enter in B1,B2,etc that will achieve this without resorting to Vlookup() All offers of help greatly accepted, and my thanks to all respondents FinanceGuru |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
Hi David,
this works fine. the only problem I have is that the cell B1 displays 30/12/00 until I enter a date in A1,then B1 displays as per the formula,and as expected How can I alter your formula so that B1 appears blank until I enter a date in A1 - as I have hundreds of renewal dates in my worksheet? FG "David Biddulph" wrote: =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1 -- David Biddulph "Finance Guru" wrote in message ... Hello, I am using Excel 2007 My problem is this, I am entering dates with a renewal date of a year in advance minus 1 day : A1 B1 30/01/08 29/01/09 20/03/08 19/08/09 01/01/08 31/12/08 01/02/09 31/01/10 and so on ..... Presently I am using Vlookup() to put the information into B1,B2,B3,etc automatically,however the date data is getting increasing long. Is there a formulua that I can enter in B1,B2,etc that will achieve this without resorting to Vlookup() All offers of help greatly accepted, and my thanks to all respondents FinanceGuru |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
Hello Niek,
this works fine. the only problem I have is that the cell B1 displays 30/12/00 until I enter a date in A1,then B1 displays as per the formula,and as expected How can I alter your formula so that B1 appears blank until I enter a date in A1 - as I have hundreds of renewal dates in my worksheet? FG "Niek Otten" wrote: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1 -- Kind regards, Niek Otten Microsoft MVP - Excel "Finance Guru" wrote in message ... | Hello, | I am using Excel 2007 | | My problem is this, I am entering dates with a renewal date of a year in | advance minus 1 day : | | A1 B1 | 30/01/08 29/01/09 | 20/03/08 19/08/09 | 01/01/08 31/12/08 | 01/02/09 31/01/10 | and so on ..... | | Presently I am using Vlookup() to put the information into B1,B2,B3,etc | automatically,however the date data is getting increasing long. | | Is there a formulua that I can enter in B1,B2,etc that will achieve this | without resorting to Vlookup() | | All offers of help greatly accepted, and my thanks to all respondents | FinanceGuru | | |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
=IF(A1="","",OriginalFormula)
-- Kind regards, Niek Otten Microsoft MVP - Excel "Finance Guru" wrote in message ... | Hello Niek, | | this works fine. the only problem I have is that the cell B1 displays | 30/12/00 until I enter a date in A1,then B1 displays as per the formula,and | as expected | | How can I alter your formula so that B1 appears blank until I enter a date | in A1 - as I have hundreds of renewal dates in my worksheet? | | FG | | | | "Niek Otten" wrote: | | =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1 | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Finance Guru" wrote in message ... | | Hello, | | I am using Excel 2007 | | | | My problem is this, I am entering dates with a renewal date of a year in | | advance minus 1 day : | | | | A1 B1 | | 30/01/08 29/01/09 | | 20/03/08 19/08/09 | | 01/01/08 31/12/08 | | 01/02/09 31/01/10 | | and so on ..... | | | | Presently I am using Vlookup() to put the information into B1,B2,B3,etc | | automatically,however the date data is getting increasing long. | | | | Is there a formulua that I can enter in B1,B2,etc that will achieve this | | without resorting to Vlookup() | | | | All offers of help greatly accepted, and my thanks to all respondents | | FinanceGuru | | | | | | | |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
Try it this way:
=IF(A1=0,"",DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1) i.e. show a blank if A1 is a blank or contains zero. Hope this helps. Pete On Feb 27, 10:16*am, Finance Guru wrote: Hello Niek, this works fine. the only problem I have is that the cell B1 displays 30/12/00 until I enter a date in A1,then B1 displays as per the formula,and as expected How can I alter your formula so that B1 appears blank until I enter a date in A1 - as I have hundreds of renewal dates in my worksheet? FG "Niek Otten" wrote: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1 -- Kind regards, Niek Otten Microsoft MVP - Excel "Finance Guru" wrote in ... | Hello, | I am using Excel 2007 | | My problem is this, I am entering dates with a renewal date of a year in | advance minus 1 day : | | A1 * * * * * * * * *B1 | 30/01/08 * * * 29/01/09 | 20/03/08 * * * 19/08/09 | 01/01/08 * * * 31/12/08 | 01/02/09 * * * 31/01/10 | and so on ..... | | Presently I am using Vlookup() *to put the information into B1,B2,B3,etc | automatically,however the date data is getting increasing long. | | Is there a formulua that I can enter in B1,B2,etc *that will achieve this | without resorting to Vlookup() | | All offers of help greatly accepted, and my thanks to all respondents | FinanceGuru | |- Hide quoted text - - Show quoted text - |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
See below.
Pete On Feb 27, 9:51*am, Finance Guru wrote: Hi David, this works fine. the only problem I have is that the cell B1 displays 30/12/00 until I enter a date in A1,then B1 displays as per the formula,and as expected How can I alter your formula so that B1 appears blank until I enter a date in A1 - as I have hundreds of renewal dates in my worksheet? FG "David Biddulph" wrote: =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))-1 -- David Biddulph "Finance Guru" wrote in message ... Hello, I am using Excel 2007 My problem is this, I am entering dates with a renewal date of a year in advance minus 1 day : A1 * * * * * * * * *B1 30/01/08 * * * 29/01/09 20/03/08 * * * 19/08/09 01/01/08 * * * 31/12/08 01/02/09 * * * 31/01/10 and so on ..... Presently I am using Vlookup() *to put the information into B1,B2,B3,etc automatically,however the date data is getting increasing long. Is there a formulua that I can enter in B1,B2,etc *that will achieve this without resorting to Vlookup() All offers of help greatly accepted, and my thanks to all respondents FinanceGuru- Hide quoted text - - Show quoted text - |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
Brilliant - Many thanks
FG "Niek Otten" wrote: =IF(A1="","",OriginalFormula) -- Kind regards, Niek Otten Microsoft MVP - Excel "Finance Guru" wrote in message ... | Hello Niek, | | this works fine. the only problem I have is that the cell B1 displays | 30/12/00 until I enter a date in A1,then B1 displays as per the formula,and | as expected | | How can I alter your formula so that B1 appears blank until I enter a date | in A1 - as I have hundreds of renewal dates in my worksheet? | | FG | | | | "Niek Otten" wrote: | | =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1 | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | "Finance Guru" wrote in message ... | | Hello, | | I am using Excel 2007 | | | | My problem is this, I am entering dates with a renewal date of a year in | | advance minus 1 day : | | | | A1 B1 | | 30/01/08 29/01/09 | | 20/03/08 19/08/09 | | 01/01/08 31/12/08 | | 01/02/09 31/01/10 | | and so on ..... | | | | Presently I am using Vlookup() to put the information into B1,B2,B3,etc | | automatically,however the date data is getting increasing long. | | | | Is there a formulua that I can enter in B1,B2,etc that will achieve this | | without resorting to Vlookup() | | | | All offers of help greatly accepted, and my thanks to all respondents | | FinanceGuru | | | | | | | |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
A Year in advance ?
Hello Pete, (& Niek David )
At last the problem of the blank cell has been solved. Just the way I wanted it to be. Thank you all for your assistance throughout my questions - it is as always very much appreciated appreciated. FinanceGuru UK "Pete_UK" wrote: Try it this way: =IF(A1=0,"",DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1) i.e. show a blank if A1 is a blank or contains zero. Hope this helps. Pete On Feb 27, 10:16 am, Finance Guru wrote: Hello Niek, this works fine. the only problem I have is that the cell B1 displays 30/12/00 until I enter a date in A1,then B1 displays as per the formula,and as expected How can I alter your formula so that B1 appears blank until I enter a date in A1 - as I have hundreds of renewal dates in my worksheet? FG "Niek Otten" wrote: =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))-1 -- Kind regards, Niek Otten Microsoft MVP - Excel "Finance Guru" wrote in ... | Hello, | I am using Excel 2007 | | My problem is this, I am entering dates with a renewal date of a year in | advance minus 1 day : | | A1 B1 | 30/01/08 29/01/09 | 20/03/08 19/08/09 | 01/01/08 31/12/08 | 01/02/09 31/01/10 | and so on ..... | | Presently I am using Vlookup() to put the information into B1,B2,B3,etc | automatically,however the date data is getting increasing long. | | Is there a formulua that I can enter in B1,B2,etc that will achieve this | without resorting to Vlookup() | | All offers of help greatly accepted, and my thanks to all respondents | FinanceGuru | |- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how to advance dates by one year then up to 10 yrs in Excel | Excel Worksheet Functions | |||
| Combination Graph with current year and prior year sales | Charts and Charting in Excel | |||
| how to creat a formula to advance date by one year | Excel Worksheet Functions | |||
| how to calculate $1000/year at 5% for 25 years showing each year | Setting up and Configuration of Excel | |||
| How to compare current year to prior year in bar chart? | Charts and Charting in Excel | |||
Linear Mode
