+ Reply to Thread
Results 1 to 4 of 4

Static Time/Date Stamp

  1. #1

    Static Time/Date Stamp

    How can I set up a Static Time/Date stamp to be posted in a cell
    whenever another specific cell has any information in it or is this
    possible? I never want the time or date to change.

    Cell "A1" is empty until I enter info into it. Probably text, not a
    number.
    Cell "A2" is the date/time stamp cell. It will show a static date as
    soon as info is
    entered into cell "A1".

    Thanks.

    Keith


  2. #2
    Jake Marx
    Guest

    Re: Static Time/Date Stamp

    Hi Keith,

    > How can I set up a Static Time/Date stamp to be posted in a cell
    > whenever another specific cell has any information in it or is this
    > possible? I never want the time or date to change.
    >
    > Cell "A1" is empty until I enter info into it. Probably text, not a
    > number.
    > Cell "A2" is the date/time stamp cell. It will show a static date as
    > soon as info is entered into cell "A1".


    You could use a UDF (user-defined function):

    Public Function DATESTAMP(WatchedCell As Range) As Variant
    If Len(WatchedCell.Value) Then
    DATESTAMP = Now()
    Else
    DATESTAMP = ""
    End If
    End Function

    In cell A2, you would enter:

    =DATESTAMP(A1)

    Although this is simple, it will change the datestamp any time the value in
    the watched cell is changed. This may not be what you want. If you don't
    want this, you'd probably want to use the Worksheet_Change event to watch
    for the first change:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
    If Len(Range("A2").Value) = 0 Then
    Range("A2").Value = Now()
    End If
    End If
    End Sub

    This one goes "behind" the worksheet. To do this, you can right-click the
    worksheet tab and select "View Code". Just paste the code above into the
    resulting codepane, and it should work.

    --
    Regards,

    Jake Marx
    www.longhead.com


    [please keep replies in the newsgroup - email address unmonitored]



  3. #3

    Re: Static Time/Date Stamp

    Jake,

    Worked like a champ. Many thanks. I am not an expert at visual basic.
    More like a beginner.

    Keith

    Jake Marx wrote:
    > Hi Keith,
    >
    > > How can I set up a Static Time/Date stamp to be posted in a cell
    > > whenever another specific cell has any information in it or is this
    > > possible? I never want the time or date to change.
    > >
    > > Cell "A1" is empty until I enter info into it. Probably text, not a
    > > number.
    > > Cell "A2" is the date/time stamp cell. It will show a static date as
    > > soon as info is entered into cell "A1".

    >
    > You could use a UDF (user-defined function):
    >
    > Public Function DATESTAMP(WatchedCell As Range) As Variant
    > If Len(WatchedCell.Value) Then
    > DATESTAMP = Now()
    > Else
    > DATESTAMP = ""
    > End If
    > End Function
    >
    > In cell A2, you would enter:
    >
    > =DATESTAMP(A1)
    >
    > Although this is simple, it will change the datestamp any time the value in
    > the watched cell is changed. This may not be what you want. If you don't
    > want this, you'd probably want to use the Worksheet_Change event to watch
    > for the first change:
    >
    > Private Sub Worksheet_Change(ByVal Target As Range)
    > If Not Application.Intersect(Target, Range("A1")) Is Nothing Then
    > If Len(Range("A2").Value) = 0 Then
    > Range("A2").Value = Now()
    > End If
    > End If
    > End Sub
    >
    > This one goes "behind" the worksheet. To do this, you can right-click the
    > worksheet tab and select "View Code". Just paste the code above into the
    > resulting codepane, and it should work.
    >
    > --
    > Regards,
    >
    > Jake Marx
    > www.longhead.com
    >
    >
    > [please keep replies in the newsgroup - email address unmonitored]



  4. #4
    Registered User
    Join Date
    11-24-2015
    Location
    Langley, BC
    MS-Off Ver
    2013
    Posts
    1

    Re: Static Time/Date Stamp

    Further to this solution, I would like to use this code applied to an individual row. what I mean is, If a value is entered into Cell C1 then the date is stamped in B1 and so on from C1 to C12. I would like that date to remain static until the value in C1 is removed. This is my very first post so if you need more information in order to help me solve my problem please ask. Thanks in advance. Kaleena

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1