| จำนวนหน้าตามหัวข้อ: < [1 2] | Stopwatch in Excel เธรดต่อผู้เขียนข้อความ: Ilka Nahmmacher
|
|---|
Oliver Walter 
สหราชอาณาจักร
Local time: 06:08
ภาษาเยอรมัน เป็น ภาษาอังกฤษ
+ ...
| 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)
Local time: 07:08
ภาษาเยอรมัน เป็น ภาษาฝรั่งเศส
+ ...
| 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
เยอรมนี
Local time: 07:08
ภาษาเยอรมัน เป็น ภาษาอังกฤษ
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)
Local time: 07:08
ภาษาเยอรมัน เป็น ภาษาฝรั่งเศส
+ ...
| 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
เยอรมนี
Local time: 07:08
ภาษาเยอรมัน เป็น ภาษาอังกฤษ
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
เยอรมนี
Local time: 07:08
ภาษาเยอรมัน เป็น ภาษาอังกฤษ
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
เยอรมนี
Local time: 07:08
ภาษาอังกฤษ เป็น ภาษาเยอรมัน
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
เยอรมนี
Local time: 07:08
ภาษาเยอรมัน เป็น ภาษาอังกฤษ
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
เยอรมนี
Local time: 07:08
ภาษาอังกฤษ เป็น ภาษาเยอรมัน
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.
| | | | | จำนวนหน้าตามหัวข้อ: < [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 | LinguaCore | AI Translation at Your Fingertips
The underlying LLM technology of LinguaCore offers AI translations of unprecedented quality. Quick and simple. Add a human linguistic review at the end for expert-level quality at a fraction of the cost and time.
More info » |
| | Trados Studio 2022 Freelance | The leading translation software used by over 270,000 translators.
Designed with your feedback in mind, Trados Studio 2022 delivers an unrivalled, powerful desktop
and cloud solution, empowering you to work in the most efficient and cost-effective way.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |
Login to reply/comment 
