Formula to add the date of the day the row is filled

[Dosce]

Hi,

I have a sheet where I keep track of some stuff, it look like that :

Date	A	B	C
20/02/2023	Stuff	Value	Value
22/02/2023	Stuff	Value	Value
22/02/2023	Stuff	Value	Value
23/02/2023	Stuff	Value	Value

I would like to find a formula to put in the "Date" column, so that when I type something in the A column in a new row, the date I am typing is automatically filled in the first column.

I tried with TODAY(), but then when I use a new row, all the dates for the first column are updated to the new day, so that is not good.

Would you have any idea ?

Thanks

 

[Joe4]

I would like to find a formula to put in the "Date" column, so that when I type something in the A column in a new row, the date I am typing is automatically filled in the first column.

If you are typing stuff into column A (note that column A is the first column), then how can you put a date into the first column? It would overwrite your entry?
Which columns are you really typing into, and which column do you really want updated?

I tried with TODAY(), but then when I use a new row, all the dates for the first column are updated to the new day, so that is not good.

The TODAY() function will always return the current date. So tomorrow, all cells that have TODAY() in it will show the new date.
What you are talking about is a Date Stamp, and usually requires VBA. There are literally thousands of posts on the topic here (this question gets asked multiple times a week).
You can either search for past posts on the topic to see how it is done, or if you clarify the question above, we can tailor something to your requirements.

 

[kvsrinivasamurthy]

Use worksheet event code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A2000")) Is Nothing Then
Application.EnableEvents = False
If Target <> "" Then Range("A" & Target.Row) = Date
Application.EnableEvents = True
End If
End Sub

How to use workheet event the code
Right click on Sheet tab --> view code
Visual Basic (VB) window opens.
Paste the code
Close the VB window.
Save the file as .xlsm

 

[Dosce]

Oh yes my choice of column names is very bad, sorry.

A	B	C	D
20/02/2023	Stuff	Value	Value
22/02/2023	Stuff	Value	Value
22/02/2023	Stuff	Value	Value
23/02/2023	Stuff	Value	Value

Thank you for the name timestamp, now I am finding results, I could not find any answer before.

@kvsrinivasamurthy , your VBA almost works, I would need the date to be in the column A when stuff is entered in column B, whereas as for now with your code the date shows in column A when I type in column A

 

[kvsrinivasamurthy]

Modified code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2:B2000")) Is Nothing Then
Application.EnableEvents = False
If Target <> "" Then Range("A" & Target.Row) = Date
Application.EnableEvents = True
End If
End Sub

 

[Dosce]

That is awesome, thank you !

I would also need the same thing for Google sheets, will the code be the same ?

 

[kvsrinivasamurthy]

I don't know about Google sheets.

 

[Joe4]

Note that all Google Sheets questions should be posted to the "General Discussion & Other Applications" forum: