Time Stamp

[rramon]

Hello,

I was wondering if it is possible to do the following:

If I start at A1 using my down arrow move to A2
in B2 display the time I entered into Cell A2
using my down arrow move to A3
in C2 display the time I exited A2 and in B3 the time I entered into A3
and so on

Thanks for the help!

 

[ajetrumpet]

are you talking about moving within a datasheet through the interface? If so, NO, there is no easy way to do this.

 

[rramon]

are you talking about moving within a datasheet through the interface? If so, NO, there is no easy way to do this.

Hey ajetrumpet,

Thanks for the reply. I didn't think it would be easy guess I'll have to learn some VBA. I'm trying to develop a timestudy worksheet for work. Again thanks for the reply. I play trombone by the way.

 

[ajetrumpet]

You never told me if you're trying to do this in Datasheet view or not. Are you? Your description was so vague, I didn't really know what to think. Tell me about this "time study" you are doing....


I play trumpet by the way. Still got chops? I lost mine years ago...

 

[rramon]

I wouldn't call them chops anymore but I try to play a few times a week.

 

[chergh]

This isn't particularly tricky. You want to use the selection change event for the work book and do it something like this.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

ws.Columns("B:C").NumberFormat = "hh:mm:ss;@"
ws.Range("B" & Target.Row) = Now()


End Sub

You only need to format the columns once to how you want the time displayed, my code formats the columns every time the event triggers, there is also little point to your the time you exited the previous cell as it will be the same time as you entered the next cell.

 

[ajetrumpet]

HA! I thought I was in an Access forum...my complete apologies to you rramon. oops!!

 

[deafmetal1]

I was able to tweak this for a date, as opposed to a time. But it only updates if I put my cursor on that row again after putting data in A1. How can I make it automatically enter the date in B1 once hit the enter key down to A2?

Here's the code I have thusfar:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

If ws.Range("A" & Target.Row) > "" Then
ws.Columns("B").NumberFormat = "ddmmmyy"
ws.Range("B" & Target.Row) = Now()
Else
If ws.Range("A" & Target.Row) = "" Then
ws.Range("B" & Target.Row) = ""

End If
End If

End Sub

This is for a financial report where I enter in an amount paid, and I want the field next to it to autopopulate with the date I entered the info. Lazy man's way of autopopulating the date I entered their payment amount.


Thank You in advance!!

 

[Brianwarnock]

After hitting enter you move down a row and that is the Target row being checked, you need to -1, this gives a problem if you try to work on row 1 but you can overcome that, however I haven't checked every situation but you might like to play with this.

Brian

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

If Target.Row = 1 Then Exit Sub
If ws.Range("A" & Target.Row - 1) > "" Then
ws.Columns("B").NumberFormat = "ddmmmyy"
ws.Range("B" & Target.Row - 1) = Now()
Else
If ws.Range("A" & Target.Row - 1) = "" Then
ws.Range("B" & Target.Row - 1) = ""

End If
End If

End Sub

 

[Brianwarnock]

BTW if you want the date to remain static, ie not changed just because a row is selected then you need to do another check, I hope its the last, being lazy or automating can get complicated

Brian

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim ws As Worksheet
Dim wb As Workbook

Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

If Target.Row = 1 Then Exit Sub
If ws.Range("A" & Target.Row - 1) > "" Then
    If IsDate(ws.Range("B" & Target.Row - 1)) Then Exit Sub
ws.Columns("B").NumberFormat = "ddmmmyy"
ws.Range("B" & Target.Row - 1) = Now()
Else
If ws.Range("A" & Target.Row - 1) = "" Then
ws.Range("B" & Target.Row - 1) = ""

End If
End If

End Sub

 

[deafmetal1]

Kewl, will try this out and see how it works. Thanks!!