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

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

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

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

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