Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: How to add the current date to a cell.?

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?



  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Regards!

    Yogi Anand, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  4. #4
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    New Member
    Join Date
    Apr 2002
    Posts
    17
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ahh figured it out, just had to change it to

    <>" "

    heh, Many thanks Dan!


    Steve



  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    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


    Kind Regards,
    Ivan F Moala From the City of Sails

  9. #9
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Millbank, London, UK
    Posts
    1,790
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ?


  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,940
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Chris,

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

    Dan

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •