Auto fill in and update current date and time whenever certain cells are filled

[Danisioni]

Excel sheet-testing 7.xlsx
	A	B	C	D	E	F	G
1	DAY	DATA					DATE/TIME
2	DAY 1	cell B2	cell C2	cell D2	cell E2		cell G2
3	DAY 2						auto-fill dd/ hh:mm:ss
4	DAY 3						auto-fill dd/ hh:mm:ss
5	DAY 4						auto-fill dd/ hh:mm:ss
Testing 2

Hi, can anyone help me get this done?

I need Excel to automatically FILL-IN and UPDATE the current Date & Time (dd/ hh:mm:ss) in cell G2, whenever any of the "DATA" cells (B2 to E2) in row 2 (Day1) is FILLED, (regardless of whether one of the other cells in the same row has already been filled).
Note 1: The same should apply for each of the rows below (day 2, day 3.. and so on infinitely).
Note 2: Filling or updating the "DATA" cells from one of the rows should NOT change/update the "DATE/TIME" cell from the other rows (above or below).
Note 3: If all the "DATA" cells of one row are empty then also the DATE/TIME cell of that row will remain empty.

Thanks

 

[Peter_SSs]

Welcome to the MrExcel board!

You could try this Worksheet_Change event code with a copy of your workbook. To implement ..
1. Manually format column G with the date/time format of your choice, then right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, rw As Range
  
  Set Changed = Intersect(Target, Columns("B:E"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each rw In Changed.Rows
      If Application.CountA(rw) = 0 Then
        Range("G" & rw.Row).ClearContents
      Else
        Range("G" & rw.Row).Value = Now
      End If
    Next rw
    Application.EnableEvents = True
  End If
End Sub

 

[Danisioni]

Excel sheet-testing 7.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	DAY	other	other	other	DATA				other	other	other	DATE/TIME
2	DAY 1				E2	F2	G2	H2				autofill date/time
3					E3	F3	G3	H3
4	DAY 2											autofill date/time
5
6	DAY 3											autofill date/time
7
Testing

Thank you so much Peter_SSs . This is the first time I use VBA code and it worked almost perfectly. Except a few things that need to be adjusted.​

Updated layout:

1. So first of all there will be a change in the layout. Here above you will find the actual layout as how I like it to be in the end. As you see I am occupying 2 rows for each day. The "DATA" area will be subdivided into 8 cells (E2 to H3). While most of the other cells will be merged into 1 as I need only 1 cell.

Important problems encountered with the code, that need to be solved:

1. While copy-pasting information into any of the data cells, the small icon with the "paste options" will disappear almost immediately, and will automatically apply only the source formatting. In a few words it will not allow me to match destination formatting as I need. (This happens only with the Data cells).
2. "Undo" doesn't work and resets every time I fill or edit any of the data cells. Meaning I cannot go back to past history once I touch those cells. (Again this happens only with the Data cells).

Other adjustments that I would like (not mandatory - less important):

1. When I empty one of the data cells, the Date/time totally disappears. - Unless of-course all Data cells of that day were already empty, I would like that the date/time reverses back to the previous registered time from the previous filled in Data cell of that day, instead of totally disappearing.

Thank you very much!

 

[Peter_SSs]

Updated layout:

1. So first of all there will be a change in the layout

I think that we can handle that.

Important problems encountered with the code, that need to be solved:

1. While copy-pasting information into any of the data cells, the small icon with the "paste options" will disappear ...
2. "Undo" doesn't work and resets ...

1. That is a consequence of using code like this. You could use 'Paste Special - Values' if you want to preserve the destination format.
2. Running code generally disables undo. You need specially written code to give you an undo feature. Further information here but you might need to do more searching if your want to try to resurrect an undo for your sheet. Not something that I will be doing in this thread I'm afraid.

Other adjustments that I would like (not mandatory - less important):

1. When I empty one of the data cells, the Date/time totally disappears.

This one is not clear to me. What if there was only one cell with data for that day (so there is a date/time in col L) and then you clear that one cell?

 

[Danisioni]

What if there was only one cell with data for that day (so there is a date/time in col L) and then you clear that one cell?

If there is only 1 cell filled, and I delete it, then the date/time can stay empty. Because there are no cells filled in for that day.
But if there are 2 of them filled in (for example E2 and F2.). E2 was the first, then came F2 and registered a new date/time.
Now I decide to delete F2, then the date/time cell will switch back to the latest time registered (so E2).
Not sure if thats even possible though.

That is a consequence of using code like this.

Can formulas be used instead of VBA code?

 

[Peter_SSs]

If there is only 1 cell filled, and I delete it, then the date/time can stay empty.

It wouldn't stay empty because it would already have a date/time in it, but it could be cleared (with vba).

But if there are 2 of them filled in (for example E2 and F2.). E2 was the first, then came F2 and registered a new date/time.
Now I decide to delete F2, then the date/time cell will switch back to the latest time registered (so E2).
Not sure if thats even possible though.

Only with vba, and that would require keeping a full record of all changes and date/times for every action taken in the data range.
Even then I suspect it would be problematic if you say deleted a prior entry and then decided that was a mistake and re-entered that same value.

Can formulas be used instead of VBA code?

No feasible way that I know of.

 

[Danisioni]

that would require keeping a full record of all changes and date/times for every action taken in the data range.

All right. Then I will let that idea go.

I think that we can handle that.

If you could send to me the code for the new layout that would be great!

Thanks a lot again for your wonderful support!
As a new member of this forum I greatly appreciate this.

 

[Peter_SSs]

If you could send to me the code for the new layout that would be great!

Give this a try.
I'm not sure that it will do exactly what you want. For example, if a row has sat 2 cells with data and there is a date/time in column L and then you delete 1 of the values in the row, column L will update to the current date/time, not stay as it was.

Further, if a cell contained, say "x" and you select that cell and re-enter "x" the date/time in column L will update, even though the actual value in the cell has not changed. This is an example of where the name Worksheet_Change is not quite what we might think it is.

Anyway, this is about the best I can do so far. ?‍

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, rw As Range, rData As Range

  Set Changed = Intersect(Target.EntireRow, Columns("E:H"), Rows("2:" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each rw In Changed.Rows
      Set rData = Intersect(rw.EntireColumn, rw.EntireRow.Cells(1).MergeArea.EntireRow)
      If Application.CountA(rData) = 0 Then
        Range("L" & rData.Row).MergeArea.ClearContents
      Else
        Range("L" & rData.Row).Value = Now
      End If
    Next rw
    Application.EnableEvents = True
  End If
End Sub

 

[Danisioni]

I'm not sure that it will do exactly what you want.

Unfortunately I cannot use this code for different reasons:

1. I cannot manually correct the time anymore (your first code allowed it). The time keeps updating to the current time whenever I try to correct it.
2. The time keeps updating whenever I edit a data cell. Which is not what I need. - What I need is that the time cell registers and displays the current time only when a new cell is filled for the first time (and not edited).
3. The other problems are that I cannot "undo" or match formatting (although there might be some workarounds to this, as you mentioned earlier).

If you can fix these things (at least the first 2), then that would be great.
If not I will continue my search.

Thanks again.

 

[Peter_SSs]

1. I cannot manually correct the time anymore (your first code allowed it). The time keeps updating to the current time whenever I try to correct it.

That was my mistake, corrected here I hope.

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Changed As Range, rw As Range, rData As Range

  Set Changed = Intersect(Target, Range("E2:H" & Rows.Count))
  If Not Changed Is Nothing Then
    Application.EnableEvents = False
    For Each rw In Intersect(Target.EntireRow, Columns("E:H")).Rows
      Set rData = Intersect(rw.EntireColumn, rw.EntireRow.Cells(1).MergeArea.EntireRow)
      If Application.CountA(rData) = 0 Then
        Range("L" & rData.Row).MergeArea.ClearContents
      Else
        Range("L" & rData.Row).Value = Now
      End If
    Next rw
    Application.EnableEvents = True
  End If
End Sub

2. The time keeps updating whenever I edit a data cell. Which is not what I need. - What I need is that the time cell registers and displays the current time only when a new cell is filled for the first time (and not edited).

This whole section is problematic and would require some sort of "cell history" to be maintained somewhere.
However, for the moment I am interested in the bold part.
Suppose cell F6 is blank on day 1, filled in with a value on day 2, cleared on day 3 and a value entered on day 4. Do you consider F6 to be filled in for the first time on day 4 or not?

3. .. I cannot "undo" or match formatting (although there might be some workarounds to this, as you mentioned earlier).

In relation to undo, any work-around I think would be very difficult.

In relation to match formatting, if the formatting in columns E:H is uniform and you always want to "match destination formatting" then the code could do that, either ..
- by copying the formatting from a dummy hidden row, or
- by applying the various formatting components directly, or
- as I mentioned before, use Paste Special - Values in the first place
If you want to keep all the paste options available after the paste has taken place then I don't think that will be possible with the code.