Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

How do I get Excel to automatically insert todays date and the current time in a cell, when I insert a value in another cell in the same row?

I know the CTRL + ; and CTRL + : shortcuts, but I would much prefer if the cell was just auto-filled.

share|improve this question

2 Answers 2

up vote 4 down vote accepted

If you're happy writing VBA you can do this with the Worksheet Change Event.

Here's some sample code which will write today's date and time in column B whenever you change a cell in column A:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim CellToChange As Range

    'Check the change happened in Column A (i.e. 1)
    If Target.Column = 1 Then
        'The Cell we want to change is on this row, but in Column B (i.e. 2)
        Set CellToChange = Target.Worksheet.Cells(Target.Row, 2)

        'Only write in Today's date and the time if the cell is empty
        If IsEmpty(CellToChange) Then
            CellToChange.Value = Now
        End If
    End If
End Sub

If you need the change to happen in different columns just change the column numbers in:

If Target.Column = 1 Then

and:

Set CellToChange = Target.Worksheet.Cells(Target.Row, 2)

To get this code on your Worksheet hit Alt+F11, double click your sheet in the tree view on the left and then paste the code into the Window that appears.

share|improve this answer
    
Ok, I was hoping for a simpler solution because it seems like a such a trivial/common requirement. However, I absolutely love VB, and you solution worked immediately. Amazing. I'd +1 you if I had the rep to do so. :) Thanks. –  Jakob Gade Sep 24 '09 at 8:31
    
Nice solution, I wasn't up to coding it at this time in the morning. –  Col Sep 24 '09 at 8:34
    
I've just realised you want the Time and Date, not just the date, so I've changed the code to use Now instead of Date. I'm glad you're happy with VBA and can use the code. –  Dave Webb Sep 24 '09 at 8:52

I guess you could pre fill the column with a formula of =if(cell1="";"";today()) but that would auto update every time you opened the spreadsheet, I don't think there's a way to get it to fill in the date at the point you fill in the cell then leave it at that date. Not without doing some programming anyway.

share|improve this answer
    
I'm affraid you're right, I have to do a tiny bit of programming to solve this one. Funny, because it seems like such a obvious feature. Thank you for your help... –  Jakob Gade Sep 24 '09 at 8:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.