Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Time elapsed application (Excel 97 SR 2)

    All,

    Here's my vision... I want an application that will allow me to push a button, and will record an elapsed time from the last time the button was pushed. I will compile these times into a spreadsheet, and chart and autocalculate. I'm looking to see an average of how much time I spend on a particular task. When the task is done, I press the button for a new task, and the clock starts over.

    I'm raring to go and tackle this, but I've kinda stalled fresh out of the gate.

    I'm trying to write the macro that will jump to column F, find the last data in the column, down arrow once to get a fresh cell, and write a new time stamp. The vba isn't doing what the help file says it should be doing, however.

    Here's what I have;

    Sub time_stamp()
    '
    ' time_stamp Macro
    ' Macro recorded 1/22/2003 by me
    '
    ' Keyboard Shortcut: Ctrl+z
    '
    Application.Goto Reference:="R1C6"
    Selection.End(xlDown).Select
    Sendkeys (down)
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("F4").Select
    End Sub

    It keeps crapping out at the sendkeys line. That's the exact syntax from the helpfile though.

    Also, unrelated to sendkeys but still part of the project... Everytime I write a "=Now()" into a cell, it removes my hh:mm:ss formatting, and puts in a mm/dd/yy hh:mm format. What's up with that?

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Time elapsed application (Excel 97 SR 2)

    There should be no need for Sendkeys, and it woul dbe interesting to know which help topic suggested it. Try th efollowing :

    Sub Time_Stamp()
    With [F65536].End(xlUp).Offset(1, 0)
    .Value = Now
    .NumberFormat = "hh:mm:ss"
    End With
    End Sub

    I am assuming that as you want a time stamp, you want the value to be static. Entering the Formula =NOW() means that the value will be updated to show the current time each time the sheet recalculates.

    Andrew C

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Time elapsed application (Excel 97 SR 2)

    Andrew is quick! Here's a slightly fancier version which adds the elapsed time calculation in Column G.

    Sub time_stamp()
    ' Keyboard Shortcut: Ctrl+z
    With ActiveSheet
    If Cells(4, 6).Value = "" Then
    Cells(3, 6).Value = "Time"
    Cells(4, 6).Value = Time
    Cells(3, 7).Value = "Elapsed"
    Else
    With Cells(.Rows.Count, 6).End(xlUp)
    .Offset(1, 0).Value = Time
    .Offset(1, 1).FormulaR1C1 = "=RC[-1]-R[-1]C[-1]"
    .Offset(1, 1).NumberFormat = "h:mm:ss"
    End With
    End If
    End With
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time elapsed application (Excel 97 SR 2)

    Wow!

    That was far more than I'd hoped for.

    Thank you both, that did the trick.

    I'm going to play with this for a while, and see how far I can get. I'm sure I'll have more questions.

    Thank you both again!

  5. #5
    Lounger
    Join Date
    Mar 2001
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Time elapsed application (Excel 97 SR 2)

    If I wanted to change my app so that it displays the ticking time, ie watching a clock and I know I need to get this thing done in the next 2 mins, etc...

    What direction can I look to take that code?

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Time elapsed application (Excel 97 SR 2)

    You could use Application.OnTime but be warned that it can slow your computer down if you don't set a reasonable interval between time checks, such as five seconds. <!post=Here,85306>Here<!/post> is an old post of mine on this, which shows the time in the statusbar, but it needs work, and needs specific work if you want to show elapsed time since last taskstart. Alternatively you could set TClockEx, up on the status bar or use some other timer (QuickBooks Pro has one, there are a number of shareware and freeeware).
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •