asked on

Trying to make Excel v2104 put in today's date with each new row

Excel v2104 in O365 opened in app. I read a lot about how to make the current date change, but not the date in a new row. Or is that what it means? The new row would automatically start with today's date?

Martin Liss

Assuming the date column is column 'A' and the data starts in row 2, and assuming that new rows (and existing rows) will have something in column 'B' then put this in A2 and copy all the way down (or for enough rows to enclose all possible potential new rows).

=IF(B2="","",TODAY())

Bert2005

ASKER

I keep getting April 43, 2038. Actually, 44338

Martin Liss

The column needs to be formatted as a date, but I just realized that my formula isn't any good since all the dates will be updated every day.

Martin Liss

You can manually put a frozen date in a cell by pressing Ctrl+;

Martin Liss

Or if you don't mind VBA, put this in the sheet's Change event.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim dteNow As Date
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Not IsEmpty(Target) Then
            dteNow = Format(Now, "mm/dd/yyyy")
            Target.Offset(0, -1) = dteNow
        End If
    End If
End Sub

Open in new window

Bert2005

ASKER

Where is the change event?

This is rather ironic project?

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Martin Liss

I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
       Experts Exchange Most Valuable Expert (MVE) 2015, 2017
       Experts Exchange Distinguished Expert in Excel 2018
       Experts Exchange Top Expert Visual Basic Classic 2012 to 2020
       Experts Exchange Top Expert VBA 2018 to 2020