Excel cell default to todays date and time

Question

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.

Answer 1

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.

Answer 2

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.