Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Insert time (Excel '07)

    I have a workbook, that has now taken off. My boss wants to put it on our company server so that everyone has access to it.
    One piece is missing however and I am reluctant to grant his wish w/o that piece. Yea like I have any real say <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15>
    Referring back to post post 744928 where I needed help in remembering how to insert some text into a cell, I now want to add time only to a text cell. I tried a reverse of post 744928, to no avail.
    Can this even be done so it shows up as something like this: "Contacted customer" 07:30 where the user enters the text only, and the time appears at the end of the text.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert time (Excel '07)

    The user can insert the current time by pressing Ctrl+Shift+;
    For example:
    - The user types the text Contacted customer followed by a space
    - The user presses Ctrl+Shift+;
    The cell now contains Contacted customer 4:34 PM

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert time (Excel '07)

    A formula can do this: =A2& " - " &TEXT(NOW(),"hh:mm")

    A2 is a cell where the contacted customers name is entered, and the rest is current time appended to the name.
    If you want time added to the end of a string, without a formula refering to a cell, it would need to be set up with an event macro.
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert time (Excel '07)

    But that formula will always display the current time, not the time when the user entered the text (and presumably contacted the customer).

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert time (Excel '07)

    Ooops... quite true.

    Quick fix - Once the formula is in the cell: Move over the cell and when the pointer changes to a 4 headed arrow, right-click and drag off and then drag back onto the cell. When the mouse button is release the right-click menu appears and then choose Copy here as values only. (Fast version of paste special - values)

    Blaah, blaah, blaah....
    That quick fix seems sooooo long to explain~!!!
    Regards,
    Rudi

  6. #6
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Insert time (Excel '07)

    thanks Rudi, Thanks Hans. I think that the event macro is what I am going to need, because I don't want the date to change, and I don't want user input other than the text in that cell.
    I already have a macro for a pop up calendar. I have attached a copy. I would rather have the calendar pop up in the date cell, but this will work.
    Attached Files Attached Files
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert time (Excel '07)

    Let's say that you want to affix the time to entries in column N.
    Right-click the sheet tab and select View Code from the popup menu.
    Copy/paste the following code into the module that appears:
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Application.EnableEvents = False
    If Not Intersect(Range("N:N"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("N:N"), Target).Cells
    If Not oCell = "" Then
    oCell = oCell & " " & Format(Time, "h:mm AM/PM")
    End If
    Next oCell
    End If
    Application.EnableEvents = True
    End Sub
    </code>
    Switch back to Excel and enter some text in a cell in column M to test the code.

  8. #8
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Insert time (Excel '07)

    Once again Hans you have saved the day. the code works perfectly in both Excel 07 and Excel 03.
    Is there a way however to separate the time stamp by about two spaces?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  9. #9
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert time (Excel '07)

    I've been following this thread as it is sort-of applicable to something I'm working on.

    The only issue I have with your method is that if you edit the cell to correct, say, a typo, the time is appended a second time.
    The present working solution I have is to put the (date and) time entered in the next column - it is always replaced/updated completely when the text cell is edited.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert time (Excel '07)

    Having the time in a separate cell would be my preference anyway.

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert time (Excel '07)

    Of course, insert another space in the string " " in the code.

  12. #12
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Insert time (Excel '07)

    Hmm, I see what you both mean. That would clean up things. So what would need to change in the code besides the Range "N:N"?
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Insert time (Excel '07)

    You could use
    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim oCell As Range
    Application.EnableEvents = False
    If Not Intersect(Range("N:N"), Target) Is Nothing Then
    For Each oCell In Intersect(Range("N:N"), Target).Cells
    If oCell = "" Then
    oCell.Offset(0, 1).ClearContents
    Else
    oCell.Offset(0, 1) = Time
    End If
    Next oCell
    End If
    Application.EnableEvents = True
    End Sub
    </code>
    This will enter the time in column O when a cell in column N is edited (if the cell in column N is cleared, the cell next to it will be cleared too).

  14. #14
    Silver Lounger t8ntlikly's Avatar
    Join Date
    Dec 2001
    Location
    Chandler, AZ
    Posts
    2,162
    Thanks
    46
    Thanked 13 Times in 11 Posts

    Re: Insert time (Excel '07)

    Ok, this is really getting cool! Works great Hans but what about adding a date to that so they don't have to touch the calendar, and will the column be sortable?
    You guys are fantastic.
    Thanks John
    Teamwork is essential; it gives the enemy other people to shoot at. (Murphy's War Laws #39)

  15. #15
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert time (Excel '07)

    To add date and time, just replace:
    oCell.Offset(0, 1) = Time
    with
    oCell.Offset(0, 1) = Date & " " & Time
    Regards,
    Rudi

Page 1 of 2 12 LastLast

Posting Permissions

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