Excel VBA - clock in buttons problem

[MikeyMike]

Hi, I am creating an automated clocking in/out system.
My spreadsheet runs on a five day week from Monday to Friday.
The spreadsheet has four boxes for each day - book in AM, book out AM,
book in PM and book out PM.

I have two buttons at the bottom of the table - Book in and Book out. I
am having problems making these buttons do what I want them to do.

I have cells containing the formulae to display the current date,
current day and current time.

I want my spreadsheet to be automated, so that when I press the 'Book
in' button, the program will pick up what day it is from my date and
day cells, as well as whether or not it is in the morning or afternoon,
according to my current time cell.

From this information I want it to record the time in the correct "Book
in AM" or "Book in PM" cell of the correct day.

What would be the best way to go about doing this. I considered using
an IF function in the programming, what's the simplest way, has anyone
done something similar before?

Thanks in advance for any feedback

 

[Tom Ogilvy]

All the date information is available in the VBA Now function, so you don't
need to depend on the cells (you would need to force a calculate event to
ensure the time value is updated on the sheet.

Dim sngTime as Single
sngTime = Now - int(now)
if sngTime >= .5 then
msgbox "PM"
else
msgbox "AM"
End if

Demo'd from the immediate window:

sngTime = Now - int(now)
? sngTime
0.33223379629635
? format(now,"hh:mm:ss")
07:58:45

it is 7:58 AM

? format(now,"mm/dd/yyyy hh:mm:ss dddd")
02/20/2004 08:00:03 Friday

 

[Jim Rech]

Assuming the "boxes" you have for time entries are cells:

Sub BookInClick()
If Hour(Now) < 12 Then
Range("BookInAM").Value = Now
Else
Range("BookInPM").Value = Now
End If
End Sub