Date and Time Populate based on another cell

[pattigander]

I have the following formula set up so that when cell A4 is populated B4 is date stamped and if A4 is blank B4 is blank. I also want the date to not change once entered. When I put in the formula I get a circular reference error. Not sure what I am doing wrong...

=IF(A4<>"",IF(B4<>"",B4,TODAY()), "")

Any hep would be appreciated..

 

[Joe4]

The best way to do TimeStamps is to use VBA. The problem with using functions like "TODAY()" is that they are dynamic and will always return the current date (they are not "frozen/locked" in time, but are constantly recalculated).

Are you agreeable to a VBA solution?
If so, is A4/B4 the only cells you want to apply this logic to, or also other cells on your sheet?
If others, please tell us the exact range this should be applied to.

 

[pattigander]

I have other cells the first is date entered in cell a4:a6 and repeated in cells A9:A50. I am also capturing time stamp when a4:a50 is populated into cell c9:c50. Any help would be greatly appreciated.

 

[Joe4]

Why use two columns for Date/Time stamp when you can do both in a single column, with a value that returns both date and time?

Here is VBA code that should so that on the range you want. Just go to the sheet that you want to apply this to, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
   
'   Specify range to monitor
    Set rng = Range("A4:A6,A9:A50")
   
'   Exit if no cells in watched range updated
    If rng Is Nothing Then Exit Sub

'   Loop through updated cells
    For Each cell In rng
        Application.EnableEvents = False
'       If cell is not blank, add date/time stamp to column B
        If cell <> "" Then
            cell.Offset(0, 1) = Now()
'       If cell is blank, clear date/time stamp from column B
        Else
            cell.Offset(0, 1).ClearContents
        End If
        Application.EnableEvents = True
    Next cell

End Sub

This should run automatically as you make entries into cells A4:A6 and A9:A50.

 

[pattigander]

Why use two columns for Date/Time stamp when you can do both in a single column, with a value that returns both date and time?

Here is VBA code that should so that on the range you want. Just go to the sheet that you want to apply this to, right-click on the Sheet tab name at the bottom of the screen, select "View Code", and paste this code in the VB Editor window that pops-up:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
  
'   Specify range to monitor
    Set rng = Range("A4:A6,A9:A50")
  
'   Exit if no cells in watched range updated
    If rng Is Nothing Then Exit Sub

'   Loop through updated cells
    For Each cell In rng
        Application.EnableEvents = False
'       If cell is not blank, add date/time stamp to column B
        If cell <> "" Then
            cell.Offset(0, 1) = Now()
'       If cell is blank, clear date/time stamp from column B
        Else
            cell.Offset(0, 1).ClearContents
        End If
        Application.EnableEvents = True
    Next cell

End Sub

This should run automatically as you make entries into cells A4:A6 and A9:A50.

Thanks. Much appreciated. It worked great.

 

[Joe4]

You are welcome.
Glad I was able to help!

 

[pattigander]

You are welcome.
Glad I was able to help!

Sorry one other quick question. How do I apply this code to multiple sheets?

 

[Joe4]

Sorry one other quick question. How do I apply this code to multiple sheets?

Copy that code into the appropriate Sheet module, for each sheet that you want to apply it to.

 

[pattigander]

Copy that code into the appropriate Sheet module, for each sheet that you want to apply it to.

Thanks

 

[Joe4]

You are welcome.