+ Reply to Thread
Results 1 to 4 of 4

Automatically enter date and time but only update once.

  1. #1
    PM
    Guest

    Automatically enter date and time but only update once.

    I have a workbook that contains 14 sheets. I have a sheet for each month
    followed by 2 sheets for information.

    Each Month sheet has the following column headings associated from columns A
    through J:-

    Owner; from date; number of days; to date, address, ID, month, input by;
    date; time.

    I have to input data in columns A, B, C E, H, I and J.

    Columns A and H are pick lists.

    I have formulas in the following columns:-

    Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)

    Column F: =IF(ISBLANK(Enn),"",+Fnn+1)

    I want column I to be populated AUTOMATICALLY (do not want to use the
    Control and semi-colon etc ) with the current date (dd mmm yy format) and
    column J to be populated with the current time (format hh:mm am/pm) only when
    column H is not blank.

    Once the date and time have been entered in columns I and J, I do not want
    it to be updated with a new time the next time someone goes into the work
    book or when the date changes the following day. It should only be populated
    to ‘blanks’ is there is no data in column H

    Additionally, I do not have any experience of creating macro’s or VBA and
    therefore the information given needs to be plain!!

    Any help greatly appreciated.


  2. #2
    Bob Phillips
    Guest

    Re: Automatically enter date and time but only update once.

    Here is some VBA to do it

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False
    On Error GoTo ws_exit
    With Target
    If .Column = 8 Then
    With .Offset(0, 1)
    .Value = Date
    .NumberFormat = "dd mmm yy"
    End With
    With .Offset(0, 2)
    .Value = Now
    .NumberFormat = "hh:mm AM/PM"
    End With
    End If
    End With

    ws_exit:
    Application.EnableEvents = True

    End Sub


    'This is worksheet event code, which means that it needs to be
    'placed in the appropriate worksheet code module, not a standard
    'code module. To do this, right-click on the sheet tab, select
    'the View Code option from the menu, and paste the code in.



    --
    HTH

    Bob Phillips

    "PM" <Pank [email protected]> wrote in message
    news:[email protected]...
    > I have a workbook that contains 14 sheets. I have a sheet for each month
    > followed by 2 sheets for information.
    >
    > Each Month sheet has the following column headings associated from columns

    A
    > through J:-
    >
    > Owner; from date; number of days; to date, address, ID, month, input by;
    > date; time.
    >
    > I have to input data in columns A, B, C E, H, I and J.
    >
    > Columns A and H are pick lists.
    >
    > I have formulas in the following columns:-
    >
    > Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)
    >
    > Column F: =IF(ISBLANK(Enn),"",+Fnn+1)
    >
    > I want column I to be populated AUTOMATICALLY (do not want to use the
    > Control and semi-colon etc ) with the current date (dd mmm yy format) and
    > column J to be populated with the current time (format hh:mm am/pm) only

    when
    > column H is not blank.
    >
    > Once the date and time have been entered in columns I and J, I do not want
    > it to be updated with a new time the next time someone goes into the work
    > book or when the date changes the following day. It should only be

    populated
    > to 'blanks' is there is no data in column H
    >
    > Additionally, I do not have any experience of creating macro's or VBA and
    > therefore the information given needs to be plain!!
    >
    > Any help greatly appreciated.
    >




  3. #3
    JE McGimpsey
    Guest

    Re: Automatically enter date and time but only update once.

    Take a look here:

    http://www.mcgimpsey.com/excel/timestamp.html


    In article <[email protected]>,
    "PM" <Pank [email protected]> wrote:

    > I have a workbook that contains 14 sheets. I have a sheet for each month
    > followed by 2 sheets for information.
    >
    > Each Month sheet has the following column headings associated from columns A
    > through J:-
    >
    > Owner; from date; number of days; to date, address, ID, month, input by;
    > date; time.
    >
    > I have to input data in columns A, B, C E, H, I and J.
    >
    > Columns A and H are pick lists.
    >
    > I have formulas in the following columns:-
    >
    > Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)
    >
    > Column F: =IF(ISBLANK(Enn),"",+Fnn+1)
    >
    > I want column I to be populated AUTOMATICALLY (do not want to use the
    > Control and semi-colon etc ) with the current date (dd mmm yy format) and
    > column J to be populated with the current time (format hh:mm am/pm) only when
    > column H is not blank.
    >
    > Once the date and time have been entered in columns I and J, I do not want
    > it to be updated with a new time the next time someone goes into the work
    > book or when the date changes the following day. It should only be populated
    > to ‘blanks’ is there is no data in column H
    >
    > Additionally, I do not have any experience of creating macro’s or VBA and
    > therefore the information given needs to be plain!!


  4. #4
    Pank Mehta
    Guest

    RE: Automatically enter date and time but only update once.

    Many thanks Bob, it works a treat.

    "PM" wrote:

    > I have a workbook that contains 14 sheets. I have a sheet for each month
    > followed by 2 sheets for information.
    >
    > Each Month sheet has the following column headings associated from columns A
    > through J:-
    >
    > Owner; from date; number of days; to date, address, ID, month, input by;
    > date; time.
    >
    > I have to input data in columns A, B, C E, H, I and J.
    >
    > Columns A and H are pick lists.
    >
    > I have formulas in the following columns:-
    >
    > Column C: =IF(ISBLANK(Cnn),"",+Bnn+Cnn)
    >
    > Column F: =IF(ISBLANK(Enn),"",+Fnn+1)
    >
    > I want column I to be populated AUTOMATICALLY (do not want to use the
    > Control and semi-colon etc ) with the current date (dd mmm yy format) and
    > column J to be populated with the current time (format hh:mm am/pm) only when
    > column H is not blank.
    >
    > Once the date and time have been entered in columns I and J, I do not want
    > it to be updated with a new time the next time someone goes into the work
    > book or when the date changes the following day. It should only be populated
    > to ‘blanks’ is there is no data in column H
    >
    > Additionally, I do not have any experience of creating macro’s or VBA and
    > therefore the information given needs to be plain!!
    >
    > Any help greatly appreciated.
    >


+ 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