How to add the current date to a cell.?

Moradyn

New Member
Joined
Apr 12, 2002
Messages
17
If A1 is blank, and A2=blank and a3= Now()

When i enter the name 'Steve' into A1, i want cell A2 to enter the current date (and stay that way forever)


Anyone know how ? :)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
You can enter the current date in any cell with CTRL+; -- this date will stay the same; however if you put =NOW() in a cell the date will be dynamic!
HTH
 
Upvote 0
If A1 is blank, and A2=blank and a3= Now()

When i enter the name 'Steve' into A1, i want cell A2 to enter the current date (and stay that way forever)


Anyone know how ?

Hi,

There's no formula which can put the current date/time in a cell which won't update when the worksheet is recalculated (unless someone can prove me wrong). Therefore, you could use a simple bit of VBA code. Right click the worksheet tab and choose View Code. Then paste this:-

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And Target.Value = "Steve" Then
Me.Range("A2").Value = Now
End If
End Sub

The procedure Worksheet_Change is fired in Excel every time you change a cell on the worksheet. All this code does it say 'if the changed cell (Target) is cell A1 and the value of Target is Steve then put the current date/time into A2'.

HTH,
Dan
 
Upvote 0
Thanks Dan, i will try this out asap...


Can you tell me what i would need to change there to make it work for a whole column, and a date..

ie: if i enter something into A2:A5000, it would add the date i entered it into B2:B5000. (respectively)

Regards

Steve
 
Upvote 0
How about this?

Let me know if it suits your needs.

Dan

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
'Only run macro if a single cell was changed
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub

If Target.Row < 5001 And Target.Column = 1 And Target.Value = "Steve" Then
    Application.EnableEvents = False
    Me.Range("B" & Target.Row).Value = Now
    Application.EnableEvents = True
End If
End Sub
 
Upvote 0
I entered that, and it works, but only for Steve, is there something that i can change to make it when something is typed in A1 say, any sort of text, like *.*

<----- vba n00bie

(as you have guessed!)

Many thanks

Steve
 
Upvote 0
On 2002-04-13 14:39, Moradyn wrote:
I entered that, and it works, but only for Steve, is there something that i can change to make it when something is typed in A1 say, any sort of text, like *.*

<----- vba n00bie

(as you have guessed!)

Many thanks

Steve

Amended

<pre/>
Private Sub Worksheet_Change(ByVal Target As Range)
'Only run macro if a single cell was changed
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then Exit Sub

If Target.Row < 5001 And Target.Column = 1 Then
Application.EnableEvents = False
Me.Range("B" & Target.Row).Value = Now
Application.EnableEvents = True
End If
End Sub

</pre>
 
Upvote 0
On 2002-04-13 14:14, dk wrote:
There's no formula which can put the current date/time in a cell which won't update when the worksheet is recalculated (unless someone can prove me wrong).

Dan, is it you or Mudface who has a tenner riding on this ?

:wink:
 
Upvote 0
Chris,

I think I might have mentioned a little wager a while ago. Why, do you have a cunning plan?

Dan
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

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
Back
Top