Solved: Stop watch in excel which just displays minutes

[helix123]

Hi,

What I would like to have is a stopwatch in excel which just displays minutes i.e. instead of 1 hour 30 min I want a macro to just show 90.

On a different forum I was able to find the following macro:


Dim SchedRecalc As Date, datStartTime As Date
Sub RecalcTimer()
Dim wbk As Workbook
Dim ws As Worksheet
Set wbk = ThisWorkbook
Set ws = wbk.Sheets(1) ' <== Change the "1" to appropriate #
'ws.Range("C3").Value = Format(Now, "dd-mmm-yy")
ws.Range("C4").Value = Format(Now - datStartTime, "hh:mm:ss")
Call SetTimer
End Sub
Sub SetTimer()
If datStartTime = 0 Then datStartTime = Now
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "RecalcTimer"
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, _
Procedure:="RecalcTimer", _
Schedule:=False
End Sub
Sub ResetTimer()
datStartTime = 0
End Sub



See attached spreadsheet which shows the buttons.

The first problem is that it shows it in the hours minutes seconds format. Is there a way to get to just show the number of minutes which have past?

Also if the timer is started and then stopped it seems that when the timer is restarted again it starts from excel's now time rather than the time at which the clock was initially.


Any help on this would be much appreciated.

Daniel

[helix123]

Sorry didn't quite finish the sentence - I was meant to say:

"Also if the timer is started and then stopped it seems that when the timer is restarted again it starts from excel's now time rather than the time at which the clock was initially stopped."

[Crocus Crow]

First problem is fixed, but I'm not quite sure if this is what you're after with regard to the second problem.

Dim SchedRecalc As Date, datStartTime As Date

Sub RecalcTimer()
ThisWorkbook.Sheets(1).Range("C4").Value = DateDiff("s", datStartTime, Now) \ 60
SetTimer
End Sub
Sub SetTimer()
If datStartTime = 0 Then datStartTime = Now
SchedRecalc = Now + TimeValue("00:00:01")
Application.OnTime SchedRecalc, "RecalcTimer"
End Sub
Sub StopTimer()
datStartTime = Now
On Error Resume Next
Application.OnTime EarliestTime:=SchedRecalc, Procedure:="RecalcTimer", Schedule:=False
End Sub
Sub ResetTimer()
datStartTime = 0
End Sub

[helix123]

Cheers for that.

[helix123]

Another solution is to use a formula

=TIMEVALUE(TEXT(C4,"[hh]:mm"))*24*60

[helix123]

Just realised don't need the brackets round the hours or with your code then leave out the *24*60