VBA worksheet_change - Date & Time in a cell

  • If anyone can answer this question .. YAY.
    I have two columns within a spreadsheet. I want a date & time value in each column depending on when the field was entered.

    My problem is using NOW() .. this updates to the current time and date for all fields within the column.

    Is there anyway to add a date & time within a field and lock the values once they are entered?

    Ultimately, I would like to calculate the exact time between the two fields.


    Tayler [the blue fox] :thanx:

  • Re: Time & Date

    I was unaware of those commands .. so it will help with my knowledge, but not with this situation.

    The officers that will enter the data into the field (besides being old school idiots) can not be trusted to enter time (for they can alter it to better their statistics) .. so I was hoping for a way that they could enter the date, and a time appear without their knowledge or .. appear automatically into the same field or another which I can lock.

    dd/mmm hh:ss is the format I am playing with .. but without the officer entering '14/12 9:56' into the field, I can not seem to get what I want.

    Is there anyway to enter a date into one field and the time appear in either the same field by way of cell format or another field, automatically entering the time that the 1st field was entered?

    Tayler [the blue fox] :thanx:

  • Re: Time & Date

    Andy -

    I think he may be looking for a programmatic solution

    How about timestamping the entry
    So when user leaves row 2 date and time are entered into out columns
    But should never recalculate for any reason


  • Re: Time & Date

    I think you maybe right marc.

    Search the forum for worksheet events.

    If you think this is true for they can alter it to better their statistics then you better not store the value in place the user can access.


  • Re: Time & Date

    I can have it set so that once they enter a loan number into column A1, column B1 inserts a date and time .. via NOW() .. BUT .. if the user then goes to A2 to enter a second loan, B2 has the current time added, but A2 also updates to the current time.

    Is there anyway to set it so that A2 stays static within a formular??
    Or is the only way via VBA coding or the manual shift+; ??

    and guys .. thanks heaps for your help on this :)

    Tayler [the blue fox] :thanx:

  • Re: Time & Date

    I don't wan to use it .. as I am only just beginning to learn it .. so I am not yet up to speed with coding.

    But .. if it will take coding to do what I want, then coding I will have to learn quicker.

    Thanks heaps boys :)

    Tayler [the blue fox] :thanx:

  • VBA worksheet_change - Date & Time

    I was given this example to add a date and time into a cell using worksheet_change.

    As far as I have been told, once I put a value into A2, B2 should return a NOW() figure.

    I have added this code into a module, but nothing happens within B2.
    Am I doing something wrong? (well clearly if it's not working) Would be great if someone could help me out.

    Basically, what I am trying to do, if an officer puts a loan number into A2, then I wish the current date and time to be put into B2. A2 is blank open opening the workbook.

    Tayler [the blue fox] :thanx:

  • Re: VBA worksheet_change - Date & Time

    [the blue fox],

    Please reference this link on how to use code tags. I will edit your post adding them.

  • Re: VBA worksheet_change - Date & Time

    dont have the code in a module place the code inside your worksheet instead

    if you double click your sheet tab or press Alt & F11 it will bring up the vb editor then double click on sheet1 and paste the code in there this should solve your problem

  • Re: VBA worksheet_change - Date & Time

    Merged threads.... Hey Foxy, can you stick to the same thread for the same topic please - saves people going over solutions already tried/suggested. Cheers & welcome to Ozgrid :)

Participate now!

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