Need Macro to enter current time and round to nearest quarter hour

[tbstein]

I created an excel attendance sheet and would like to double click on a cell in a range and have the current time entered and rounded to the nearest quarter hour. I need to be able to record clients arrival time at the day program that I work at.

 

[dirkgeraedts]

hi,

Try this. You'll need to paste it in the worksheet's code page, not into a module. If you need to round up or round down you can replace mround by the functions CEILING or FLOOR.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)


Cancel = True
Target.Value = Application.WorksheetFunction.MRound(Now, 1 / (24 * 4)) - Int(Now)


End Sub

 

[Snakehips]

tbstein,

My alternate offering is...

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target = Round((Time - Int(Time)) * 96, 0) / 96
End Sub

Recipient cells formatted for time.

 

[tbstein]

Thank you, Thank you, Snakehips. This works perfectly! I have been trying to figure this out for hours and you have solved it in minutes. Thanks, again!