Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
I need to know if there is anyway to update a date annually, not daily. In
other words, say today is 8/20/06. I need the date to be updated automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one cell because I need to use it in a calculation. -- Thanks, Cheryl |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
Cheryl,
Write the first 2 years dates in the worksheet. Mark those 2 cells, and drag it all down or to the left. Then the annuals will be updated. -- Cheryl skrev: I need to know if there is anyway to update a date annually, not daily. In other words, say today is 8/20/06. I need the date to be updated automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one cell because I need to use it in a calculation. -- Thanks, Cheryl |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
Hi Roar,
Thanks for the input but I actually need it in one cell and for that cell to automatically update on the anniversary of the date. Sort of like a TODAY function works, but not daily, only annually. I think what you're referring to would work as a copying action. I'm not even sure what I need to do can be done in Excel. Hopefully, I'm wrong. Thanks, -- Cheryl "Roar" wrote: Cheryl, Write the first 2 years dates in the worksheet. Mark those 2 cells, and drag it all down or to the left. Then the annuals will be updated. -- Cheryl skrev: I need to know if there is anyway to update a date annually, not daily. In other words, say today is 8/20/06. I need the date to be updated automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one cell because I need to use it in a calculation. -- Thanks, Cheryl |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
Well, then you may continue as follows,
Make the series of the relevant dates as I described some place in the worksheet, e.g. from cell A1 to M1 In the one cell, insert the function =LOOKUP(TODAY(),A1:M1) Cheryl skrev: Hi Roar, Thanks for the input but I actually need it in one cell and for that cell to automatically update on the anniversary of the date. Sort of like a TODAY function works, but not daily, only annually. I think what you're referring to would work as a copying action. I'm not even sure what I need to do can be done in Excel. Hopefully, I'm wrong. Thanks, -- Cheryl "Roar" wrote: Cheryl, Write the first 2 years dates in the worksheet. Mark those 2 cells, and drag it all down or to the left. Then the annuals will be updated. -- Cheryl skrev: I need to know if there is anyway to update a date annually, not daily. In other words, say today is 8/20/06. I need the date to be updated automatically on 8/20/07, 8/20/08, etc. It would need to be entered into one cell because I need to use it in a calculation. -- Thanks, Cheryl |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
Hi Cheryl, I saw your post yesterday & didn't quite solve it but your comment "I'm not even sure what I need to do can be done in Excel. Hopefully, I'm wrong." provided a little extra motivation ;-) I think I have it now... Try entering this: *cell A1: "=today()" *cell B1: "8/20/2006" (depending on formatting of cell *cell C1: =DATE(YEAR($B$1)+DATEDIF($B$1,$A1,"y"),MONTH($B$1) ,DAY($B$1)) Or to put it all in one cell: "=DATE(2006+DATEDIF(DATE(2006,8,20),TODAY(),"y"),8 ,20)" For background on the Datedif function check out Chip's page: http://www.cpearson.com/excel/datedif.htm (he includes links to other Date And Time Related Procedures at the base of the page) hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=573658 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
Wow! The all-in-one-cell formula did it. I did all kinds of date
manipulations with my computer and it updated correctly each time Very cool....I'll check out that page on the Datedif function. Glad the challenge motivated you further ;-) Thanks much -- Cheryl "broro183" wrote: Hi Cheryl, I saw your post yesterday & didn't quite solve it but your comment "I'm not even sure what I need to do can be done in Excel. Hopefully, I'm wrong." provided a little extra motivation ;-) I think I have it now... Try entering this: *cell A1: "=today()" *cell B1: "8/20/2006" (depending on formatting of cell *cell C1: =DATE(YEAR($B$1)+DATEDIF($B$1,$A1,"y"),MONTH($B$1) ,DAY($B$1)) Or to put it all in one cell: "=DATE(2006+DATEDIF(DATE(2006,8,20),TODAY(),"y"),8 ,20)" For background on the Datedif function check out Chip's page: http://www.cpearson.com/excel/datedif.htm (he includes links to other Date And Time Related Procedures at the base of the page) hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=573658 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
Pleased I could help :-) As far as I can tell the only thing that will upset the formula is if your date eg 8/20/2006 is greater than the today() result - & this is explained in the link. hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=573658 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel 2002 automated annual date change
Hi Rob,
You were right. When I tested the formula, it doesn't work if the date you need inputed is in the future which some of mine are. Fortunately, I was able to work with it by using the previous anniversary date, ex 12/17/05, for your formula in column D. In column E, I used the formula =DATE(YEAR(D5)+1, MONTH(D5), DAY(D5)), which I got from the Web site you referred me to. That updated correctly, and I was able to do the rest of my calculations with it. So, it ended up needing 2 cells, instead of 1, because of the future date problem. Not to bad, I think. Once again, thanks for your help and if you ever figure out how to get it to work in one cell, let me know. I've never done very complicated (arrays, etc) formulas, so this is pretty new to me! -- Cheryl "broro183" wrote: Pleased I could help :-) As far as I can tell the only thing that will upset the formula is if your date eg 8/20/2006 is greater than the today() result - & this is explained in the link. hth Rob Brockett NZ Always learning & the best way to learn is to experience... -- broro183 ------------------------------------------------------------------------ broro183's Profile: http://www.excelforum.com/member.php...o&userid=30068 View this thread: http://www.excelforum.com/showthread...hreadid=573658 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to prevent file date change when no update made in Excel? | Excel Discussion (Misc queries) | |||
How do I flag a cell in Excel 2002 when the date entered has pass | Excel Discussion (Misc queries) | |||
Changes in date functions between excel 2002 and 2003 | Excel Worksheet Functions | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Cells User Select Locked after upgrade to Excel 2002 | Excel Discussion (Misc queries) |