Easiest way to save today's date automatically?

[Chartist]

I make many entries in an Excel sheet. The first column in the date. It's 99% the same date as time of entry ('today').

I can use TODAY() but it will change tomorrow to tomorrow's date.

How to automatically save today's date (as a value, I guess) by default for every new entry?

(PS: another thread said: Ctrl+; enters today's date - this is good but not a solution as it still requires data entry anyway.)

 

[offthelip]

You can put this code into the worksheet code for that sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Cells(Target.Row, 1) = Now()
End Sub

 

[Shesil]

Method without using VBA:

Go To File->Options->Formulas->Tick "Enable iterative calculation" box and change "Maximum iterations" to 1. Then try the below formula in the sheet.


=IF(B2<>"",IF(A2="",NOW(),A2),"")

Note: A column for date and B column for the entry that requires timestamping.

Hope this helps.

 

[Chartist]

You can put this code into the worksheet code for that sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
Cells(Target.Row, 1) = Now()
End Sub

Changing Now() to Today() is not working, any ideas?

Also, how do I apply this to an exact range like B6:B500 instead of it working everywhere for 1st column?

Thanks!

 

[Chartist]

Method without using VBA:

Go To File->Options->Formulas->Tick "Enable iterative calculation" box and change "Maximum iterations" to 1. Then try the below formula in the sheet.


=IF(B2<>"",IF(A2="",NOW(),A2),"")

Note: A column for date and B column for the entry that requires timestamping.

Hope this helps.

Interesting - but this will then apply to all formulas in the sheet right? It's a complex sheet with lots of formulas.

 

[offthelip]

this will do it.:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B6:B500")) Is Nothing Then
Cells(Target.Row, 1) = Now()  ' or Date() if you just want the date 
End If
End Sub

There is a difference between EXCEL worksheet functions and VBA functions. VBA has a lot of functions that can be called directly from the VBA code. NOW() is the vba function that returns the date and time. An alternative function that you could use is DATE()
You can call worksheet functions from VBA but you have to prefix them like this (using the MIN function):

Application.WorksheetFunction.Min(myRange)

Today() is An excel worksheet function and is not available in VBA this is because NOW() or DATE() do the same job.
The list of worksheet functions that are available in VBA is given in the link:
List of worksheet functions available to Visual Basic

 

[Chartist]

this will do it.:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B6:B500")) Is Nothing Then
Cells(Target.Row, 1) = Now()  ' or Date() if you just want the date
End If
End Sub

There is a difference between EXCEL worksheet functions and VBA functions. VBA has a lot of functions that can be called directly from the VBA code. NOW() is the vba function that returns the date and time. An alternative function that you could use is DATE()
You can call worksheet functions from VBA but you have to prefix them like this (using the MIN function):

Application.WorksheetFunction.Min(myRange)

Today() is An excel worksheet function and is not available in VBA this is because NOW() or DATE() do the same job.
The list of worksheet functions that are available in VBA is given in the link:
List of worksheet functions available to Visual Basic

Thanks, learnt something today!
The date is working correctly now.

Any idea how to make this code work (enter today's date) in a range like B6:B5000?
Thanks!

 

[offthelip]

You can change this code in two ways to limit the ranges it works over. The first range is the range where it is detecting a change. i..e if you change a cell in this range then it will write the date soemwhere:
this line controls that bit of the code :

If Not Intersect(Target, Range("B6:B500")) Is Nothing Then

This means the code only runs if a cell in the range B6 to B500 is changed . Change this to any range except the range you are going to write the date into. ( this will cause a loop and excel will crash)
The second range is where you wrtie the date, this is controled by this line of code:

Cells(Target.Row, 1) = Now()  ' or Date() if you just want the date

The code currently uses the same row as the change enterd by the user and writes into column 1 : (Target.Row = same row) , (1= column A)
to write into column B you just need to change the 1 to a 2. However since this is currently detecting chagnes in column B just changing 1 to 2 isn't going to work.
I hope allows yo uto make the modification you need.

 

[Chartist]

You can change this code in two ways to limit the ranges it works over. The first range is the range where it is detecting a change. i..e if you change a cell in this range then it will write the date soemwhere:
this line controls that bit of the code :

If Not Intersect(Target, Range("B6:B500")) Is Nothing Then

This means the code only runs if a cell in the range B6 to B500 is changed . Change this to any range except the range you are going to write the date into. ( this will cause a loop and excel will crash)
The second range is where you wrtie the date, this is controled by this line of code:

Cells(Target.Row, 1) = Now()  ' or Date() if you just want the date

The code currently uses the same row as the change enterd by the user and writes into column 1 : (Target.Row = same row) , (1= column A)
to write into column B you just need to change the 1 to a 2. However since this is currently detecting chagnes in column B just changing 1 to 2 isn't going to work.
I hope allows yo uto make the modification you need.

I tried:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("C6:C500")) Is Nothing Then

Cells(Target.Row, 1) = Date

End Sub

This gives a 'Block If without End if' error popup.

Also, I'm unable to edit or delete the values in the date range, once it writes the date.

Any ideas? thanks!

 

[offthelip]

if you look at my post #6 you can see where to put the end if.

Also, I'm unable to edit or delete the values in the date range, once it writes the date.

My code will not cause this to happen, it just writes a value into a cell. Is there any other code you have running or run??