Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automatically fill in current date? (Excel 2000)

    I have a user who has created a To Do list in Excel. He has a Due Date, Description field, etc. He also has a [Current] Date field which he would like to populate with the date the To Do item was entered. Is there any way to do this automatically. I know that he could manually go to Insert | Function and choose the Date formula, but he would prefer an automatic date entry.

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

    Re: Automatically fill in current date? (Excel 2000)

    Ctrl+; (Ctrl+semicolon) is a very fast way to enter the current date in a cell.

    An alternative would be to create a small macro that inserts a new row and fills in the date field. This macro could be assigned to a toolbar button and/or a keyboard shortcut for ease of use.

    It might be possible to create a worksheet-level event handler to fill in the date, but that seems tricky to me; perhaps one of the Excel gurus will come up with a workable solution.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Posts
    304
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automatically fill in current date? (Excel 2000)

    Is that an automatically updating field? We found out the hard way that NOW automatically updates and he doesn't want that.

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

    Re: Automatically fill in current date? (Excel 2000)

    Ctrl+; inserts the current date as a value, not as a formula.
    Similarly, Ctrl+Shift+; inserts the current time as a value, not as a formula.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically fill in current date? (Excel 2000)

    Worksheet level event handler
    This macro added to the sheet object of the desired sheet in vb, will automatically enter today's date in col A (=col#1) whenever anything is added/changed in col B (=col#2). Change the date and todo col as desired in the code.

    Steve
    <pre>Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    Dim iDateCol As Integer
    Dim iToDoCol As Integer
    Dim rCell As Range
    iDateCol = 1
    iToDoCol = 2

    If Not Intersect(Target, Columns(iToDoCol)) Is Nothing Then
    For Each rCell In Intersect(Target, Columns(iToDoCol))
    Cells(rCell.Row, iDateCol).Value = Date
    Next
    End If
    End Sub</pre>


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

    Re: Automatically fill in current date? (Excel 2000)

    Steve,

    I was thinking along that line, but the reason I wrote that I found it "tricky" is that this will update the date each time you edit the "to do" item. I don't think that is desirable. Perhaps we need to set the date only in empty cells:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim iDateCol As Integer
    Dim iToDoCol As Integer
    Dim rCell As Range
    iDateCol = 1
    iToDoCol = 2
    If Not Intersect(Target, Columns(iToDoCol)) Is Nothing Then
    For Each rCell In Intersect(Target, Columns(iToDoCol))
    If Cells(rCell.Row, iDateCol).Value = "" Then
    Cells(rCell.Row, iDateCol).Value = Date
    End If
    Next
    End If
    End Sub

    Satiria will have to decide which version to use.

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Automatically fill in current date? (Excel 2000)

    Good Point and a simple "fix".
    I was aware of it, but figured an edit was like an add. If you change an item it needs a new date, but again as you said, Satiria can decide what works best in the application.

    Steve

Posting Permissions

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