Discussion Locked

Time problem in Excel

0 helpful
7 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Im trying to create a "clocking in" program for staff in Excel. I've had no problem creating the basic structure which allows the user to check box next to their name which then enters the system time. However, i'm not sure how to make the time "absolute". It's still dynamic after its been entered. I know that i can double cllick on the time then press F9 and this seems to do the trick - but i can't expect users to have to do this every time they "clock in". Have had no luck with macro either. Does anyone have any ideas?
Thanks

7 replies

by larry h.

Try the following macro. You might want to use a different time format, but this should do what you want.

Sub TimeMacro()
'
' TimeMacro Macro
' Macro to place current date and time in a
' cell and format as time.
'
Dim dblTime As Double dblTime = Now
ActiveCell.FormulaR1C1 = dblTime
Selection.NumberFormat = "h:mm AM/PM"
End Sub

by pvp

I don't play with VBA and/or macros, but manually, you'd want to Copy, then "Paste Special...Values" for the cell just modified.

That way the cell will no longer contain a formula to be updated, but will contain the time value.

by srobertson

Try using,

'Activecell.Value = Format(Now,"HH:NN:SS")'

This will place the current TimeValue into the Activecell. Change the formating to suit yourself remebering that in VBA "MM" refers to the Month not Minutes hence the "NN".

Scott.

This discussion is closed to new replies.