| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Update date every two weeks when a sheet is opened
Hi
I have a date in a cell (next delivery date). We get the item every second Tuesday. I am looking for (possibly) a function that updates this date every time the sheet opens, so currently the next delivery date is 05/05/09 and it will be delivered again on 19/05/09. So if the sheet is opened on 06/05/09 the date should read 19/05/09. Is this possible in Excel Your help would be much appreciated. Eddie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update date every two weeks when a sheet is opened
Lets start with an un-used column, say column J.
In J1 enter: 12/30/2008 (or the format of your choice0 in J2 enter: =J1+14 and copy down to about J100 we see: 12/30/2008 1/13/2009 1/27/2009 2/10/2009 2/24/2009 3/10/2009 3/24/2009 4/7/2009 4/21/2009 5/5/2009 5/19/2009 6/2/2009 6/16/2009 6/30/2009 7/14/2009 7/28/2009 8/11/2009 8/25/2009 9/8/2009 9/22/2009 10/6/2009 10/20/2009 11/3/2009 11/17/2009 .... etc. This goes into the year 2016. Then enter: =OFFSET(J1,MATCH(TODAY(),J1:J100,1),0) and format as Date -- Gary''s Student - gsnu200848 " wrote: Hi I have a date in a cell (next delivery date). We get the item every second Tuesday. I am looking for (possibly) a function that updates this date every time the sheet opens, so currently the next delivery date is 05/05/09 and it will be delivered again on 19/05/09. So if the sheet is opened on 06/05/09 the date should read 19/05/09. Is this possible in Excel Your help would be much appreciated. Eddie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update date every two weeks when a sheet is opened
On Apr 22, 5:38*pm, Gary''s Student
wrote: Lets start with an un-used column, say column J. In J1 enter: 12/30/2008 (or the format of your choice0 in J2 enter: =J1+14 and copy down to about J100 we see: 12/30/2008 1/13/2009 1/27/2009 2/10/2009 2/24/2009 3/10/2009 3/24/2009 4/7/2009 4/21/2009 5/5/2009 5/19/2009 6/2/2009 6/16/2009 6/30/2009 7/14/2009 7/28/2009 8/11/2009 8/25/2009 9/8/2009 9/22/2009 10/6/2009 10/20/2009 11/3/2009 11/17/2009 ... etc. This goes into the year 2016. *Then enter: =OFFSET(J1,MATCH(TODAY(),J1:J100,1),0)and format as Date -- Gary''s Student - gsnu200848 " wrote: Hi I have a date in a *cell (next delivery date). We get the item every second Tuesday. I am looking for (possibly) a function that updates this date every time the sheet opens, so currently the next delivery date is 05/05/09 and it will be delivered again on 19/05/09. So if the sheet is opened on 06/05/09 the date should read 19/05/09. Is this possible in Excel Your help would be much appreciated. Eddie Thanks Gary But could not get this to work J100 goes into 2013 which is plenty but there is a #N/A error when the function is applied to say cell C1. Eddie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update date every two weeks when a sheet is opened
On Apr 22, 8:19*pm, wrote:
On Apr 22, 5:38*pm, Gary''s Student wrote: Lets start with an un-used column, say column J. In J1 enter: 12/30/2008 (or the format of your choice0 in J2 enter: =J1+14 and copy down to about J100 we see: 12/30/2008 1/13/2009 1/27/2009 2/10/2009 2/24/2009 3/10/2009 3/24/2009 4/7/2009 4/21/2009 5/5/2009 5/19/2009 6/2/2009 6/16/2009 6/30/2009 7/14/2009 7/28/2009 8/11/2009 8/25/2009 9/8/2009 9/22/2009 10/6/2009 10/20/2009 11/3/2009 11/17/2009 ... etc. This goes into the year 2016. *Then enter: =OFFSET(J1,MATCH(TODAY(),J1:J100,1),0)and format as Date -- Gary''s Student - gsnu200848 " wrote: Hi I have a date in a *cell (next delivery date). We get the item every second Tuesday. I am looking for (possibly) a function that updates this date every time the sheet opens, so currently the next delivery date is 05/05/09 and it will be delivered again on 19/05/09. So if the sheet is opened on 06/05/09 the date should read 19/05/09. Is this possible in Excel Your help would be much appreciated. Eddie Thanks Gary But could not get this to work J100 goes into 2013 which is plenty but there is a #N/A error when the function is applied to say cell C1. Eddie Hi Garys Student Any other ideas on this one please? Thanks Eddie |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Update date every two weeks when a sheet is opened
On Apr 23, 8:12*pm, wrote:
On Apr 22, 8:19*pm, wrote: On Apr 22, 5:38*pm, Gary''s Student wrote: Lets start with an un-used column, say column J. In J1 enter: 12/30/2008 (or the format of your choice0 in J2 enter: =J1+14 and copy down to about J100 we see: 12/30/2008 1/13/2009 1/27/2009 2/10/2009 2/24/2009 3/10/2009 3/24/2009 4/7/2009 4/21/2009 5/5/2009 5/19/2009 6/2/2009 6/16/2009 6/30/2009 7/14/2009 7/28/2009 8/11/2009 8/25/2009 9/8/2009 9/22/2009 10/6/2009 10/20/2009 11/3/2009 11/17/2009 ... etc. This goes into the year 2016. *Then enter: =OFFSET(J1,MATCH(TODAY(),J1:J100,1),0)and format as Date -- Gary''s Student - gsnu200848 " wrote: Hi I have a date in a *cell (next delivery date). We get the item every second Tuesday. I am looking for (possibly) a function that updates this date every time the sheet opens, so currently the next delivery date is 05/05/09 and it will be delivered again on 19/05/09. So if the sheet is opened on 06/05/09 the date should read 19/05/09. Is this possible in Excel Your help would be much appreciated. Eddie Thanks Gary But could not get this to work J100 goes into 2013 which is plenty but there is a #N/A error when the function is applied to say cell C1. Eddie Hi Garys Student Any other ideas on this one please? Thanks Eddie- Hide quoted text - - Show quoted text - Just an update Got this sorted, my own stupidity Thanks for your help Eddie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| calculate a date, 10 weeks after a set date & return a specific da | Excel Worksheet Functions | |||
| Counting Closed Date < 2 weeks of Open Date etc | Excel Worksheet Functions | |||
| Display a update date on a sheet for a shared excel workbook | Excel Worksheet Functions | |||
| Automatic update of graph for 13 weeks | Charts and Charting in Excel | |||
| calculate weeks from a start date ( not yr weeks) | Excel Worksheet Functions | |||
Linear Mode
