Automatic date entry in cell?

**mrs179** · 07-16-2008, 09:47 AM

I have a workbook with several sheets, each of which represents a client. At the bottom of this sheet, I have a call log to record the times that we attempted communication, left a message, etc. Since it's tedious enough as it is, I'd like to save my team 2 seconds and a slight bit of that tedium by having the date an attempt was made automatically recorded in the row where the attempt is entered.

Let's say this all takes place in A47:D47. For example, if I called the client on 7/15/08 and left a message, I would record who was called in B47, the name of the caller (me) in C47, and the result in D47. If possible, I would like to have the date of the attempt (today's date on the given day) entered in A47 upon the entry of information in B47:D47.

Let me know if that's not clear, or possible. THANKS.

**mackinlay** · 07-16-2008, 10:06 AM

I know this is quite crude and I'm sure it could be improved, but if you have a function like:

=IF(AND(A47<>"", B47<>"",C47<>""),TODAY(),"")

Then as long as there is data in all three cells, the date will be displayed in the cell (you could put it in cell D47).

If you wanted the date and the time to be displayed then you could replace =TODAY() with =NOW().

I hope this is a start for you

Alex

**mrs179** · 07-16-2008, 10:27 AM

"Crude" only because it isn't sweet-looking VBA, but that's exactly what I wanted. Never knew about the "NOW" function! Thanks a lot!

**mrs179** · 07-16-2008, 10:31 AM

I guess it didn't quite work now that I look at it. So, the date and time will update to the current d/t, as opposed to the d/t the info was entered. For example, I inputted info into three different rows at three different times, and right now all of the d/t cells I tested this in turned to 9:29AM.

Any ideas for how to freeze the d/t at the time of entry?

**royUK** · 07-16-2008, 10:33 AM

The TODAY() & NOW() Functions are volatile & will change whenever the workbook calculates.

**mackinlay** · 07-16-2008, 10:35 AM

yeah, of course, i didnt think about that. I had just considered that it would update as it was entered... not too sure how to get the time to almost 'imprint' itself on the worksheet. I will have a think...!

**mrs179** · 07-16-2008, 10:37 AM

...and I just found out the Microsoft help says as much - should have checked that first. Sure enough, any time I make changes to any part of the sheet, the date/time refreshes.

Anyone have other ideas for how to "stamp" the d/t at the point of data entry without having it refresh like that?

**royUK** · 07-16-2008, 11:11 AM

You will need VBA. Use the WorkSheet_Change event

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Or Target.Column <> 2 Then Exit Sub
    Target.Offset(0, -1).Value = Date
    Target.Offset(0, 1).Value = Environ("username")
    Traget.Offset(0, 2).Select
End Sub

Right Click on the sheet tab
Choose "view code"
In the VB Editor set the left hand drop down to WorkSheet
In the VB Editor set the right hand drop down to Change
Paste in the code