enter time into file for a time sheet

  • Thread starter Thread starter Kirk
  • Start date Start date
K

Kirk

how can I enter the time automatically into a cell.
perhaps a macro. hilte the cell and hit your macro or
enter?
I want to round the time to the nearest 1/4 hour.
I also want to be able to override the automatic function
 
The quick way to enter current time is Ctrl plus the colon (date is Ctrl
plus semi-colon) but this doesn't round to the nearest 15 minutes. I think
there is an add-in that would do that as a worksheet function. Here is a
subroutine that might do what you want:

Sub RndToQtrHr()
Dim currMin As Long
Dim M As Long
Dim plusHour As Boolean
currMin = Minute(Time)
plusHour = False
If currMin < 7 Then
M = 0
ElseIf currMin < 22 Then
M = 15
ElseIf currMin < 37 Then
M = 30
ElseIf currMin < 52 Then
M = 45
Else
M = 0
plusHour = True
End If

If plusHour = False Then
ActiveCell.Value = Format$((TimeSerial(Hour(Time), M, 0)), "H:MM am/pm")
End If

If plusHour = True Then
ActiveCell.Value = Format$((TimeSerial(Hour(Time) + 1, M, 0)), "H:MM am/pm")
End If

End Sub
 
Back
Top