Automatically Change Date every 2 weeks
 

I have pretty much ripped out my hair trying to figure this out.
There's gotta be a way. Please help!!

I have a spreadsheet in which there is one cell I would like to have
automatically change every two weeks.

For example, on 12/30/2005, I would like this cell to automatically
change to 01/12/2006. Then, on 01/13/2006, I would like this cell to
automatically change to 01/26/2006.

Is there any way to do this? I assume I will need some kind of VB
programming.
:confused:


--
GYRO
------------------------------------------------------------------------
GYRO's Profile: http://www.excelforum.com/member.php...o&userid=29994
View this thread: http://www.excelforum.com/showthread...hreadid=496848

Automatically Change Date every 2 weeks
 

gyro -

The simplest way to do this is with an auto open sub on the Thisworkbook
module. Perhaps like:

Private Sub Workbook_Open()
With Sheet1.Cells(1, 1)
If .Value < Date Then .Value = .Value + 13
End With
End Sub

- voodooJoe

use an auto open sub to examine the value and change it if it mneets
"GYRO" wrote in message
...

I have pretty much ripped out my hair trying to figure this out.
There's gotta be a way. Please help!!

I have a spreadsheet in which there is one cell I would like to have
automatically change every two weeks.

For example, on 12/30/2005, I would like this cell to automatically
change to 01/12/2006. Then, on 01/13/2006, I would like this cell to
automatically change to 01/26/2006.

Is there any way to do this? I assume I will need some kind of VB
programming.
:confused:


--
GYRO
------------------------------------------------------------------------
GYRO's Profile:
http://www.excelforum.com/member.php...o&userid=29994
View this thread: http://www.excelforum.com/showthread...hreadid=496848

Automatically Change Date every 2 weeks
 

=TODAY()+MOD(DATE(2006,1,12)-TODAY(),14) should do it.


"GYRO" wrote:


I have pretty much ripped out my hair trying to figure this out.
There's gotta be a way. Please help!!

I have a spreadsheet in which there is one cell I would like to have
automatically change every two weeks.

For example, on 12/30/2005, I would like this cell to automatically
change to 01/12/2006. Then, on 01/13/2006, I would like this cell to
automatically change to 01/26/2006.

Is there any way to do this? I assume I will need some kind of VB
programming.
:confused:


--
GYRO
------------------------------------------------------------------------
GYRO's Profile: http://www.excelforum.com/member.php...o&userid=29994
View this thread: http://www.excelforum.com/showthread...hreadid=496848

Automatically Change Date every 2 weeks
 

Thanks VooDooJoe. I tried what you suggested but that doesn't seem t
work to well. It seems I would need some kind of formula or somethin
in the cell first and VB programming behind it..

--
GYR
-----------------------------------------------------------------------
GYRO's Profile: http://www.excelforum.com/member.php...fo&userid=2999
View this thread: http://www.excelforum.com/showthread.php?threadid=49684

Automatically Change Date every 2 weeks
 

Ooops. I didn't see the post by bpeltzer. Hey, I tried this formul
and it does actually work. Wow!! I totally thought that I was goin
to need VB programming but I guess not. Thank you very muc
bpeltzer..

--
GYR
-----------------------------------------------------------------------
GYRO's Profile: http://www.excelforum.com/member.php...fo&userid=2999
View this thread: http://www.excelforum.com/showthread.php?threadid=49684

Automatically Change Date every 2 weeks
 

NA Wrote:
gyro -

The simplest way to do this is with an auto open sub on the
Thisworkbook
module. Perhaps like:

Private Sub Workbook_Open()
With Sheet1.Cells(1, 1)
If .Value < Date Then .Value = .Value + 13
End With
End Sub

- voodooJoe

use an auto open sub to examine the value and change it if it mneets
"GYRO" wrote in
message
...

I have pretty much ripped out my hair trying to figure this out.
There's gotta be a way. Please help!!

I have a spreadsheet in which there is one cell I would like to have
automatically change every two weeks.

For example, on 12/30/2005, I would like this cell to automatically
change to 01/12/2006. Then, on 01/13/2006, I would like this cell
to
automatically change to 01/26/2006.

Is there any way to do this? I assume I will need some kind of VB
programming.
:confused:


--
GYRO

------------------------------------------------------------------------
GYRO's Profile:
http://www.excelforum.com/member.php...o&userid=29994
View this thread:
http://www.excelforum.com/showthread...hreadid=496848
Hi NA,
The trouble with this is:
Instead of just keeping two weeks ahead of todays date.
It Advances two weeks EVERY time you open the WorkBook.
So if you open the WorkBook four times today, the date would be
advanced eight weeks instead of two weeks.


--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile: http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=496848

Automatically Change Date every 2 weeks
 

dp -

you wrote:
The trouble with this is:
Instead of just keeping two weeks ahead of todays date.
It Advances two weeks EVERY time you open the WorkBook.
So if you open the WorkBook four times today, the date would be
advanced eight weeks instead of two weeks.



actually, the value only changes ONE TIME EVERY 2 WEEKS

With Sheet1.Cells(1, 1)
If .Value < Date Then .Value = .Value + 13
End With

say val = 1/1/06 & today is 1/4/06
val < date so val changes to 1/1/06 + 13 = 1/14/06

when you open again on 1/4 or on 1/5 or on 1/13 val (1/14) is greater than
Date, so nothin happens
when you open on 1/15, val < date so date advances 2 weeks.

the MOD formula from bpeltzer, however, is clearly a better solution.


"Desert Piranha"
<Desert.Piranha.20xn0m_1136079600.9669@excelforu m-nospam.com wrote in
message news:Desert.Piranha.20xn0m_1136079600.9669@excelfo rum-nospam.com...

NA Wrote:
gyro -

The simplest way to do this is with an auto open sub on the
Thisworkbook
module. Perhaps like:

Private Sub Workbook_Open()
With Sheet1.Cells(1, 1)
If .Value < Date Then .Value = .Value + 13
End With
End Sub

- voodooJoe

use an auto open sub to examine the value and change it if it mneets
"GYRO" wrote in
message
...

I have pretty much ripped out my hair trying to figure this out.
There's gotta be a way. Please help!!

I have a spreadsheet in which there is one cell I would like to have
automatically change every two weeks.

For example, on 12/30/2005, I would like this cell to automatically
change to 01/12/2006. Then, on 01/13/2006, I would like this cell
to
automatically change to 01/26/2006.

Is there any way to do this? I assume I will need some kind of VB
programming.
:confused:


--
GYRO

------------------------------------------------------------------------
GYRO's Profile:
http://www.excelforum.com/member.php...o&userid=29994
View this thread:
http://www.excelforum.com/showthread...hreadid=496848
Hi NA,

--
Desert Piranha


------------------------------------------------------------------------
Desert Piranha's Profile:
http://www.excelforum.com/member.php...o&userid=28934
View this thread: http://www.excelforum.com/showthread...hreadid=496848