Stamp a Cell with Time Q
 

I have listed in Sheet5 A1:A7, days i.e. Monday (A1) through Sunday
(A7). How would I via code, stamp in Sheet5 B1:B7 the current time
when an action is performed, depending on the Day of the week - 1
(minus one)?

For example, now is Sunday at 19:58, thus when I perfom an action I
wish B6 (Sat) to be poplulated with 19:58, tomorrow if I perform the
same action, say at 10:07, I want B7 (Sun) to be populated. (the info
I'm reporting on is always a day before the actual day)

The only 'quirk' I want is that if the time is after 21:00 on the day
I want the appropriate cell in B1:B7 left blank

Hope the above is not too confusing

Thanks

Stamp a Cell with Time Q
 

A worksheet change function like the one below should work. Format sheet 5
column B for time (any time format you prefer). I placed the code below on
sheet 1 in the VBA project window. Worksheet change must be placed in every
worksheet you want the action to take place. If you place the code on sheet
5 then you have to add an if statement to ignore range A1:B7.

Sub worksheet_change(ByVal Target As Range)

Myrow = Weekday(Now() - 1, vbMonday)
If Hour(Now()) < 21 Then
Sheets("Sheet5").Cells(Myrow, "B") = _
Now()
End If
End Sub

"Sean" wrote:

I have listed in Sheet5 A1:A7, days i.e. Monday (A1) through Sunday
(A7). How would I via code, stamp in Sheet5 B1:B7 the current time
when an action is performed, depending on the Day of the week - 1
(minus one)?

For example, now is Sunday at 19:58, thus when I perfom an action I
wish B6 (Sat) to be poplulated with 19:58, tomorrow if I perform the
same action, say at 10:07, I want B7 (Sun) to be populated. (the info
I'm reporting on is always a day before the actual day)

The only 'quirk' I want is that if the time is after 21:00 on the day
I want the appropriate cell in B1:B7 left blank

Hope the above is not too confusing

Thanks

Stamp a Cell with Time Q
 

Put the following code in a standard code module, then call it from a
button on a toolbar or worksheet. It formats the cell's number format
automatically as each value is entered, or you could simply format the
cells in column B ahead of time.

'----------------------------------------------------------------------
Public Sub StampTime()
Dim dblNow As Double
Dim dblTimeNow As Double
Dim lngDayOfWeek As Long
Dim lngStampRow As Long
Dim rngStampCell As Range

dblNow = Now

With WorksheetFunction
dblTimeNow = dblNow - .RoundDown(dblNow, 0)
lngDayOfWeek = .Weekday(.RoundDown(dblNow, 0))
End With

lngStampRow = ((lngDayOfWeek + 4) Mod 7) + 1

Set rngStampCell = Sheet5.Cells(lngStampRow, 2)

With rngStampCell
If (dblTimeNow (21# / 24#)) _
Then
.ClearContents
Else
.Value = dblNow
.NumberFormat = "hh:mm"
End If
End With
End Sub
--
Regards,
Bill Renaud

Stamp a Cell with Time Q
 

Bill: How does your code automaticaly update sheet 5 times. Your code only
works if somebody manually runs the macro. Weekday is a VBA function and
doesn't have to be called as a worksheetfunction.

"Bill Renaud" wrote:

Put the following code in a standard code module, then call it from a
button on a toolbar or worksheet. It formats the cell's number format
automatically as each value is entered, or you could simply format the
cells in column B ahead of time.

'----------------------------------------------------------------------
Public Sub StampTime()
Dim dblNow As Double
Dim dblTimeNow As Double
Dim lngDayOfWeek As Long
Dim lngStampRow As Long
Dim rngStampCell As Range

dblNow = Now

With WorksheetFunction
dblTimeNow = dblNow - .RoundDown(dblNow, 0)
lngDayOfWeek = .Weekday(.RoundDown(dblNow, 0))
End With

lngStampRow = ((lngDayOfWeek + 4) Mod 7) + 1

Set rngStampCell = Sheet5.Cells(lngStampRow, 2)

With rngStampCell
If (dblTimeNow (21# / 24#)) _
Then
.ClearContents
Else
.Value = dblNow
.NumberFormat = "hh:mm"
End If
End With
End Sub
--
Regards,
Bill Renaud

Stamp a Cell with Time Q
 

I guess it wasn't clear to me that the OP wanted these cells
automatically updated every time somebody entered something in ANY cell,
so I left it this way to provide more flexibility. If it needs to be
called anytime any cell is changed on this same worksheet (or any other
worksheet, for that matter), then put this code in the Worksheet_Change
event handler for this (or any other) worksheet (it simply calls the
StampTime routine):

'----------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)
StampTime
End Sub

As a matter of programming flexibility, this is a better way to do event
routines anyway. I can print out the standard code module, and have
virtually all of the code, without having to go check each worksheet and
chart code module.
--
Regards,
Bill Renaud