Automatically adding today's date, but not updating when ope

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65
Hi there.
I'm not sure if this can be done, but I thought I would ask.

I would like cell in D2 to add todays date when any text is entered into Colum A2 and not have that date update the next time I open the worksheet.

When I use =TODAY() the cell will update when ever I open the workbook.

Also I would like F2 to calculate 6 weeks from a date that I enter into E2 and change the entire row Yellow when that date is reached.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Re: Automatically adding today's date, but not updating when

Hi Steve:
....
I'm not sure if this can be done, but I thought I would ask.

I would like cell in D2 to add todays date when any text is entered into Colum A2 and not have that date update the next time I open the worksheet.

When I use =TODAY() the cell will update when ever I open the workbook.
....
In response to the first part of your question, I suggest you try the following VBA solution ...
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column <> 1 Then Exit Sub
    If Not (IsNumeric(Target.Value)) Then Range("D2") = Date
End Sub
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Then you need a vba code like
to the sheet module
Code:
Private OldData

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldData = Target.Value
End Sub

Private Sub Woorksheet_Change(ByVal Target As Range)
With Target
   If .Address(0,0) <> "A2" Then Exit Sub
   If .Value <> OdlData Then
      Application.EnableEvents = False
      Range("D2").Value = Now()
      Application.EnableEvents = True
   End If
End With
End Sub
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Re: Automatically adding today's date, but not updating when

....
Also I would like F2 to calculate 6 weeks from a date that I enter into E2 and change the entire row Yellow when that date is reached.

In response to second part of your question, you can use Conditional Formatting ...

1) Select entire row 2
2) invoke Conditional Formatting
3) use formula is ... =TODAY()>=DATE(YEAR($E$2),MONTH($E$2)+6,DAY($E$2))
Pattern Yellow

I hope this helps!
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65

ADVERTISEMENT

Thanks for the quick responses.

It's been a long time since I've had to work with Excel and I've forgotten most of what I knew.

Where do I put the VBA Codes listed above?

I know it's Tools>Macros>Vsual Basic Editor, but what from there?
Is it a module or attached to the worksheeet?

Do I need to use both of them?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
From Excel;

1) right click on the sheet tab then [ViewCode]
2) paste the code one of them
3) click x to close the window to get back to Excel

-1 Yogi's will work when anything other than number entered in columnA
-2 Mine only works when A2 changed from previos entered value in A2
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65

ADVERTISEMENT

Re: Automatically adding today's date, but not updating when

....

1) Select entire row 2
2) invoke Conditional Formatting
3) use formula is ... =TODAY()>=DATE(YEAR($E$2),MONTH($E$2)+6,DAY($E$2))
Pattern Yellow

That worked great for that part of it, but I would like the row to remain clear or white if nothing is entered into E2.
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65
From Excel;

1) right click on the sheet tab then [ViewCode]
2) paste the code one of them
3) click x to close the window to get back to Excel

-1 Yogi's will work when anything other than number entered in columnA
-2 Mine only works when A2 changed from previos entered value in A2

Sorry Jindon;

I tried both of them just like you said and I get nothing.
Also I would like this to work for any row not just row 2,
If I enter something in A3 or A15 it will enter the date in D3 or D15 respectively.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Make sure that you paste the code onto sheet module inquestion.
Code:
Private OldData

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
OldData = Target.Value
End Sub

Private Sub Woorksheet_Change(ByVal Target As Range)
With Target
   If .Column <> 1 Then Exit Sub
   If .Value <> OdlData Then
      Application.EnableEvents = False
      .Offset(,3).Value = Now()
      Application.EnableEvents = True
   End If
End With
End Sub
 

Rainbow

Board Regular
Joined
Oct 3, 2003
Messages
65
I still got nothing.

I right clicked on my "Porject Partitions" tab.

Clicked on "View Code"

The VB Editor opened up with my Sheet 1 (Project Partition) Highlighted.

I pasted the code in the screen to the right that was open.

Excel.jpg


Then closed the VB Editor.

I also tried inserting a module from Insert>Module and pasting the code into that and I still got nothing in Colum D.

I've put VB Code in in the past and it has worked, but it has been over 2
 

Forum statistics

Threads
1,141,022
Messages
5,703,788
Members
421,316
Latest member
Cyril Beki

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top