Static Date & Time Corresponding To Cell Change

Important Notice

Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

  • Hi All,

    I have two related queries
    Don’t know whether this is possible


    What I need is lets say I have 3 columns Name, Date & Time

    Name field has a drop down menu having a list of names

    Now what I need is - lets say from the dropdown menu someone selects a name - THEN at the same instance the Time & Date fields gets populated with the Date & Time of that update.

    and that Date & Time shouldn’t change if someone selects the same or different name in the row below

    Is it possible ?

    Kindly assist in lay man terms as I am a novice to Excel


    In this format what I need is like above , the start date & start time should update real time ONLY when a name is selected

    and when the End date is selected the End time , Date Elapsed & Time Elapsed should Populate automatically.

    However there should be NO effect if data is entered or changed in the Request Id or Issue Cells - basically the triggers should only be Name field & End date Field

    Is this possible ?

    Can you attach an example sheet as well please

    Have enclosed an sample attachment

    Thanks in advance

  • Re: Static Date & Time Based On Update Sequence

    Right click on the sheet name tab, choose View Code and paste in;

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Column = 1 Then
            Target(1, 2) = Time
            Target(1, 3) = Date
        End If
    End Sub
  • Re: Static Date & Time Corresponding To Cell Change

    Though the time is coming where date is and vice versa , its just a minor thing that I can fix - hey I need to use my brain some where right.

    Thanks a lot Dave - I dont know what I would have done without this site and people like you.

    Dave if its not too much can you give a solution for the Request 2

    I think its a wee bit more complicated - if you can create a code without any changes to the present format - example of request 2 is also entered in the above enclosed work sheet.

    Once again thanks mate

  • Re: Static Date & Time Corresponding To Cell Change

    Further to Dave's code:

    You will also need to format your columns:
    D & F as date
    E & G as time
    H as a number
    I as "[h]:mm" (or "[h]:mm:ss")

    This will work when anybody selects a cell in the end date column (F)


  • Re: Static Date & Time Corresponding To Cell Change

    This is an awesome site - and such nice people willing to share - Thanks Cheeky Charlie

    And to think I was running around after so called experts in my work place for the last few weeks : :) thanks all

    I have few more queries and I want to ask them all togather but I wont flood one post a day

    Cheers guys[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]It works great Cheeky

    howevr instead of both date and time coming in one cell can I have them seperate as per my example

    I guess both are coming togather because of the CODE now

    Is it possible to break it up?

    Thanks[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]I found that I can isolate the time by formating the cell to Time

    however what can be done about the start date in the Request 2 example


    couple of Added queries

    1) is it possible not to have an auto date fill at the " End Date: trigger " Instead to let the trigger be activated only after a date has been entered or after a prompt- to avoid any accidental trigger (eg the cell is clicked accidentally while browinsg through)

    2) if I want to create an alarm / alert so that I would be notified if the date elapsed exceeds say 2 days - using TODAY()>($A$1-2) in cell I or J how would I do it so that both the code & formulas dont clash.

    Refr Link : Creating An Alarm Based On Date Expiry[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Is my last post a bit confusing ? if it is i'll be happy to rework on it

    Cheers[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]

  • Re: Static Date & Time Corresponding To Cell Change

    Crikey, you've a lot to say...
    First, thanks for your response, it makes all the difference when people are grateful.
    Second, If you read my post, I do tell you how to format the columns in your worksheet so it looks how you asked (I am very thorough...)

    If this doesn't make sense, please do ask the so called experts :wink: in your office - this is quite basic.



    and when the End date is selected the End time , Date Elapsed & Time Elapsed should Populate automatically.

    It's what you asked for!!!

    Anyway, a quick solution would be to insert a couple of lines like this:

    If Target.Column = 6 Then
        if target.value = "end" then 'this is an added line
            Target = Date 
            Target(1, 2) = Now 
            Target(1, 3) = "=RC[-2]-RC[-4]" 
            Target(1, 4) = "=RC[-2]-RC[-4]"
        end if  'so is this
        End If

    This would make your code trigger if someone types "end" (without quotes) in the end date column.

    Fourth: Your last question should absolutely be another post - remember how this forum is designed to help people to find solutions to their problems. I would also suggest you search on this topic heavily before you ask a question - as far as I can tell it has been covered quite thoroughly already.


  • Re: Static Date & Time Corresponding To Cell Change

    yeah, delete the line

    Coding is generallyvery logical - it's not hard to understand because it's crazy or irrational, like a woman, it's hard to understand because you need to learn a language in order to use it - like... um, well, something else.

  • Re: Static Date & Time Corresponding To Cell Change

    Thanks again Cheeky

    I had tried removing only ".AutoFit" however gave an error.

    Thought "Columns("B:C")" more important because in the entire code thats the only place where column name is mentioned.

    Guess I was a tad stupid

    Thanks anyway for the propmt help.[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]Now I got it the Columns are represented by number (1,2,3) and not Char like A, B ,C.

    While at it can you clarify these queries as well or shall I create a new thread ?

    I'll ask in a new thread incase it helps someone else

  • Re: Static Date & Time Corresponding To Cell Change

    Sorry, I wasn't entirely clear (you have very responsibly put a low knowledge level down and I ignored it utterly! Sorry):

    Delete the line.

    The whole line with autofit on it.

    Not just .autofit


  • Re: Static Date & Time Corresponding To Cell Change

    General postscript:

    For those reading this thread, the target(y,x) which confused bluecondor temporarily is effectively an abbreviated form of the offset property which can be very easily understood from the VBA help files.

    It has slightly different syntax in that target(y,x) has the target at 1,1 whereas offset starts from target = 0,0. This is probably easier to understand like this:

    Target(4, 5) = Target.Offset(3, 4)
    Target(0, 0) = Target.Offset(-1, -1) '!

    I could understand a justification for using the longer wording, it makes it slightly easier to "read" as you have the offset written explicitly, so helps you understand what the numbers are for. The difference in speed is minimal, but that would depend on what you're doing...


  • Re: Static Date & Time Corresponding To Cell Change

    Just to be clear on this, it's not an abbreviated form of the offset property. It's an abbreviated form of the item property. The reason this syntax is permissable is that the default property of the range object is the item property....

    That might sound pedantic, but there are clear differences.

    As you mentioned, the item property is 1-based.

    Also, the item property will only ever return a single cell range, whereas the offset property may return a multi cell range. For example, compare these two:

    Sub foo()
        MsgBox Range("a1:c1")(2).Address
        MsgBox Range("a1:c1").Offset(1).Address
    End Sub

    Also, note that these two return different results:

    MsgBox Range("a1:d1")(2).Address
        MsgBox Range("a1:a4")(2).Address

    The differences are less apparent when working with a single-cell parent range, but I think that precise understanding is required to avoid confusion.

    MsgBox Target(1, 1).Address

    Is the same as/shorthand for:

    MsgBox Target.Item(1, 1).Address


  • Re: Static Date & Time Corresponding To Cell Change




    in this example - people at a level for thisthread are not likely to follow the full explanation.

  • Re: Static Date & Time Corresponding To Cell Change

    I understand where you are coming from and I'm all for keeping things simple, but introducing the notion that it is effectively the same as the offset property only confuses the situation. People who are learning from this thread would be learning incorrectly and will run into problems later on. These two properties are different to each other, just as they are different to the cells property, and each should be treated separately.

    Taking a simple example:

    Private Sub Worksheet_Change(ByVal Target As Range)
        MsgBox "item " & Target(1).Address
        MsgBox "offset " & Target.Offset(0).Address
    End Sub

    If one pastes this into a sheet class module and then selects a multicell range, say A1:A10, types in the formula =1 and completes the entry with CTRL+SHIFT+ENTER, the two properties yield different results. They are not effectively the same.


  • Re: Static Date & Time Corresponding To Cell Change

    Hey, let's not get grumpy with each other. In principle we agree; you are 100% right that offset is not the same. I think you understand that the point of my post was to explain to people who couldn't "decipher" the code how what is effectively an offset is being achieved. (That is why bluecondor could not easily tweak the code to his own spec).

    Anyone that gets this far will hopefully be able to understand the difference between "something" and "effectively achieving something". I thought I covered in my original post on this concept, but to be fair, I had left it a little ambiguous (even with the italics!)

    Ultimately, nobody will be any the worse off for reading your thorough dissection and I wouldn't remove it if it were up to me.

    As for all the examples proving the same point... I guess it makes it inescapably clear! :wink:

  • Re: Static Date & Time Corresponding To Cell Change

    No grumpiness here and I certainly didn't mean to convey any either... and who said anything about removing it? ;)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!