Jtdylktuy

I've been working on a workbook for personal use for a while now and one of the sheets tracks my hours. My hours are all input manually and calculated automatically. What I would like to accomplish is linking a cell to a check box or button and recording the time the box was checked. For example when clock in, I would check a box or press a button and the time would be recorded, but not updated. I'm not very familiar with macros or VBA's yet so any help would be appreciated.

Edit: I've created a formula based on if the boxes are checked and the current time is after the times described in the Clock In and Clock Out columns.
This is the formula in cell R14: =IF(AND(AND(N14,NOW()>O14),AND(P14,NOW()>=Q14)),Q14-O14,NOW()-O14)
The check boxes in N14 and P14 are linked to the cells they reside in and the values in cells O14 and Q14 are just time values formatted to display the clock in date and time
This is what I'm working with so far

Since you didn't provide any example on how you want it to look, or how the current tracking is done, I just made some kind of time tracker, tracking seconds.

So I did a timer thusly:

Add a button, and 00:00:00 as the time in cell where you want to keep track of time.

In my case this is the cell C2, I call this the timerPos.

I then use cell A1 or any other spare cell to keep track of time and if I'm recording, I call this the trackerPos.

Then when I press my button, this is how it will look initially:

When I'm done keeping track of time, I press the button again. The tracker is cleared, and the timer is updated:

Now, the most important part; the code for the button.

Sub timer()

Dim timeL As Long, timerPos As Range, trackerPos As Range



Set timerPos = Range("C2")

Set trackerPos = Range("A1")



If trackerPos.Value = "" Then

    trackerPos.Value = Now

Else

    timeL = Abs(Now - trackerPos.Value) * 86400

    timerPos.Value = DateAdd("s", timeL, timerPos.Value)

    timerPos.NumberFormat = "[h]:mm:ss"

    trackerPos.Value = ""

End If



End Sub

Usage:
The variables timerPos and trackerPos can be easily changed to suit your needs on where you want to keep the timer.

The first line of the Else part converts the difference in time between the current time and the time in the tracker.

The second line adds the time to the timer. The third line keeps formatting from showing dates when going beyond 24 hours.

Edit

If we want to limit the amount of time recorded for each recording to 8 hours, we only need to add a small If.

8 Hours is 28800 seconds, so we check if timeL is larger than this number, and if so, set it to this number.

    timeL = Abs(Now - trackerPos.Value) * 86400

    If timeL > 28800 Then

        timeL = 28800

    End If

    timerPos.Value = DateAdd("s", timeL, timerPos.Value)

While we are at it, I added some changing of color and text to the button, for visual aid that the timer is ticking.

Sub timer()

Dim timeL As Long, timerPos As Range, trackerPos As Range, button As Variant



Set button = ActiveSheet.Buttons("Button 1") 'Make sure this is the right button

Set timerPos = Range("C2")

Set trackerPos = Range("A1")



If trackerPos.Value = "" Then

    trackerPos.Value = Now

    button.Characters.Text = "Stop timer"

    button.Font.ColorIndex = 3

Else

    timeL = Abs(Now - trackerPos.Value) * 86400

    If timeL > 28800 Then

        timeL = 28800

    End If

    timerPos.Value = DateAdd("s", timeL, timerPos.Value)

    timerPos.NumberFormat = "[h]:mm:ss"

    trackerPos.Value = ""

    button.Text = "Start timer"

    button.Font.ColorIndex = 50

End If



End Sub