Pages in topic:   < [1 2]
Stopwatch in Excel
Thread poster: Ilka Nahmmacher
Oliver Walter
Oliver Walter  Identity Verified
United Kingdom
Local time: 17:44
German to English
+ ...
I already do that Jan 6, 2015

I have been using Excel for that purpose for several years. The keyboard shorcuts (at least English Excel 2000 and Excel 2010) are:

Ctrl+Semicolon for the date now; it's actually a number representing the number of days since 1 Jan 1900, formatted as a date, so 6 January 2015 is also 42010

Ctrl+Shift+Semicolon for the time now; it's actually a number representing a fraction of a day between 0.0000 and 1.0000, formatted as hh:mm. Example: midday is 0.5 or 12:00 .
<
... See more
I have been using Excel for that purpose for several years. The keyboard shorcuts (at least English Excel 2000 and Excel 2010) are:

Ctrl+Semicolon for the date now; it's actually a number representing the number of days since 1 Jan 1900, formatted as a date, so 6 January 2015 is also 42010

Ctrl+Shift+Semicolon for the time now; it's actually a number representing a fraction of a day between 0.0000 and 1.0000, formatted as hh:mm. Example: midday is 0.5 or 12:00 .

Then you need some fairly simple formulae to calculate elapsed times from the start and end time cells, and to add all the elapsed times to give the total time for a translation job.
I often also have extra columns in which I enter the number of words translated up to that point, and Excel calculates the incremental and/or average speeds (words/hour), the percentage done and an estimate of the time remaining for completion.

I presented an example with all of these features at a ProZ Powwow in London in 2009.

Oliver
Collapse


 
Platary (X)
Platary (X)
Local time: 18:44
German to French
+ ...
How to place the buttons Jan 6, 2015

Hello Ilka !

You wrote :


I do not know how to place two buttons on my QAT to run the VBA code



Very simple, like this (not my Tuto, but nothing more needed) :

https://www.youtube.com/watch?v=nfNBYQjbs2c

As you see, not difficult at all.

The next step, a sample code in a next post.

Kind regards, and Happy new year !

[Modifié le 2015-01-06 16:53 GMT]


 
Ben Senior
Ben Senior  Identity Verified
Germany
Local time: 18:44
German to English
Your macro Jan 6, 2015

Hi Ilka,

I'm just about to send you an email with the macro and installation instructions in a ZIP file.

Try out the macro and let me know if it works please. I've checked the macro on Excel 2007 and 2013, but I don't have access to 2003 any more, so it's not tested on your system.

Regards
Ben


 
Platary (X)
Platary (X)
Local time: 18:44
German to French
+ ...
Sample code Excel 2003 Jan 6, 2015

Hello Ilka !

I could use the following two subs with XL 2003. Assign the first one to the Start button and the second one to the Pause button.

The start button works in the active cell, so be careful where to start.


Sub demarre()

nouveau = Timer
ancien = ActiveCell * 24 * 3600
c = ActiveCell.Address
fin = False
Do While Not fin
Range(c) = Format((Timer + ancien - nouveau) / 3600 / 24, "h
... See more
Hello Ilka !

I could use the following two subs with XL 2003. Assign the first one to the Start button and the second one to the Pause button.

The start button works in the active cell, so be careful where to start.


Sub demarre()

nouveau = Timer
ancien = ActiveCell * 24 * 3600
c = ActiveCell.Address
fin = False
Do While Not fin
Range(c) = Format((Timer + ancien - nouveau) / 3600 / 24, "hh:mm:ss")
DoEvents
Loop

End Sub


Sub arret()
fin = True
End Sub


How it looks on a worksheet. Feel free to format the time cells as you wish. The DUREE column is as number (in minutes) formatted for rate calculation.

PROJET MOTS PRIX TOTAL EFFECTUÉ DÉBUT FIN DURÉE GAIN TAUX POURCENTAGE

ENGINS 1000 0,20 200,00 50 0:00:00 0:10:52 11 10,00 € 55,21 € 5%
GEOLOC 2684 0,22 590,48 0:00:00 0 0,00 € #DIV/0! 0%

The timer can restart at the registered duration after Excel is closed and reopened, by pressing the Start button.

Enjoy it if it works !
Collapse


 
Ilka Nahmmacher
Ilka Nahmmacher  Identity Verified
Germany
Local time: 18:44
German to English
TOPIC STARTER
Thank you to Ben, Oliver and Adrien Jan 7, 2015

Dear Excel experts,

Thank you all for your help! I will look into each of your solutions toward the end of the week or on the weekend.

Have a good rest of your week,
Ilka


 
Ben Senior
Ben Senior  Identity Verified
Germany
Local time: 18:44
German to English
Timer macro Jan 11, 2015

Here's the two macros for the job timer:

Public Sub StartTimer()
Dim iR As Integer
Dim rCell As Range
Dim StartTime As Date

If Range("BA1") = "" Then Range("BA1") = "Timer"
If Range("BB1") = "" Then Range("BB1") = "Started"
If Range("BC1") = "" Then Range("BC1") = "Stopped"
If Range("BD1") = "" Then Range("BD1") = "Time worked"

iR = ActiveCell.Row
Range("BA"
... See more
Here's the two macros for the job timer:

Public Sub StartTimer()
Dim iR As Integer
Dim rCell As Range
Dim StartTime As Date

If Range("BA1") = "" Then Range("BA1") = "Timer"
If Range("BB1") = "" Then Range("BB1") = "Started"
If Range("BC1") = "" Then Range("BC1") = "Stopped"
If Range("BD1") = "" Then Range("BD1") = "Time worked"

iR = ActiveCell.Row
Range("BA" & CStr(iR)).Value = "started"
Range("D" & CStr(iR)).Interior.ColorIndex = 35
StartTime = Time
Range("BB" & CStr(iR)).Value = StartTime
Range("BC" & CStr(iR)).Value = ""
Range("BD" & CStr(iR)).Value = ""
End Sub

'########################################

Public Sub StopTimer()
Dim dblTotalTime As Double
Dim dblTimeWorked As Double
Dim dblProjectPrice As Double
Dim dblHourlyRate As Double
Dim iR As Integer
Dim StartTime As Date
Dim StopTime As Date
Dim TimeWorked As Date
Dim sinTimeArray, Result As Single

iR = ActiveCell.Row
dblTotalTime = Range("D" & CStr(iR)).Value
Range("D" & CStr(iR)).Interior.ColorIndex = 38
Range("BA" & CStr(iR)).Value = "stopped"
StopTime = Time
Range("BC" & CStr(iR)).Value = StopTime
StartTime = Range("BB" & CStr(iR)).Value
TimeWorked = StopTime - StartTime
sinTimeArray = Split(TimeWorked, ":")
dblTimeWorked = sinTimeArray(0) + ((sinTimeArray(1) * 100) / 60) / 100
Range("BD" & CStr(iR)).Value = TimeWorked
dblTotalTime = dblTotalTime + dblTimeWorked
Range("D" & CStr(iR)).Value = dblTotalTime
dblProjectPrice = Range("C" & CStr(iR)).Value
If dblTotalTime 0 Then
dblHourlyRate = dblProjectPrice / dblTotalTime
End If
Range("E" & CStr(iR)).Value = dblHourlyRate
End Sub

The ranges used will need to be adapted to the columns in the spreadsheet and the columns required are:
Project price, Accumulated time, Hourly rate, Timer, Started, Stopped and Time worked.

Ben
Collapse


 
Rolf Keller
Rolf Keller
Germany
Local time: 18:44
English to German
Caution Jan 12, 2015

Ben Senior wrote:

If dblTotalTime 0 Then
dblHourlyRate = dblProjectPrice / dblTotalTime
End If


The above will cause a crash.

ProZ eats up your angle brackets, so you need a different notation:

If dblTotalTime = 0 Then
dblTotalTime = 0
Else
dblHourlyRate = dblProjectPrice / dblTotalTime
End If


 
Ben Senior
Ben Senior  Identity Verified
Germany
Local time: 18:44
German to English
Crash? Jan 12, 2015

Rolf Keller wrote:

Ben Senior wrote:

If dblTotalTime 0 Then
dblHourlyRate = dblProjectPrice / dblTotalTime
End If


The above will cause a crash.

ProZ eats up your angle brackets, so you need a different notation:

If dblTotalTime = 0 Then
dblTotalTime = 0
Else
dblHourlyRate = dblProjectPrice / dblTotalTime
End If



I don't understand your reasoning here Rolf. This macro runs in Excel not ProZ so the angle brackets are not a problem in Microsoft VBA. In all of my testing within Excel I've not experienced any problems. I did make the mistake of not using "& lt" and "& gt" so here's the revised code snippet formatted for ProZ

If dblTotalTime <> 0 Then
dblHourlyRate = dblProjectPrice / dblTotalTime
End If

Which achieves the same as your snippet. Thanks for pointing out the ProZ bug

Ben


 
Rolf Keller
Rolf Keller
Germany
Local time: 18:44
English to German
Hm Jan 12, 2015

Ben Senior wrote:

I don't understand your reasoning here Rolf. This macro runs in Excel not ProZ so the angle brackets are not a problem in Microsoft VBA. In all of my testing within Excel I've not experienced any problems


I don't understand YOUR reasoning. Apparently, you typed angle brackets but ProZ' editor slurred them. So, if somebody copies your macro code verbatim from this web page, that macro is definitely faulty and will crash in Excel. That's what I said.

BTW, similar effects can occur if you use square brackets.


 
Pages in topic:   < [1 2]


To report site rules violations or get help, contact a site moderator:


You can also contact site staff by submitting a support request »

Stopwatch in Excel






Anycount & Translation Office 3000
Translation Office 3000

Translation Office 3000 is an advanced accounting tool for freelance translators and small agencies. TO3000 easily and seamlessly integrates with the business life of professional freelance translators.

More info »
Protemos translation business management system
Create your account in minutes, and start working! 3-month trial for agencies, and free for freelancers!

The system lets you keep client/vendor database, with contacts and rates, manage projects and assign jobs to vendors, issue invoices, track payments, store and manage project files, generate business reports on turnover profit per client/manager etc.

More info »