Vom Thema belegte Seiten: < [1 2] | Stopwatch in Excel Initiator des Themas: Ilka Nahmmacher
| Oliver Walter Vereinigtes Königreich Local time: 22:43 Deutsch > Englisch + ... 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: 23:43 Deutsch > Französisch + ... 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 Deutschland Local time: 23:43 Deutsch > Englisch
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: 23:43 Deutsch > Französisch + ... 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 Deutschland Local time: 23:43 Deutsch > Englisch THEMENSTARTER 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 Deutschland Local time: 23:43 Deutsch > Englisch
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 Deutschland Local time: 23:43 Englisch > Deutsch
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 Deutschland Local time: 23:43 Deutsch > Englisch
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 Deutschland Local time: 23:43 Englisch > Deutsch
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. | | | Vom Thema belegte Seiten: < [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 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 » |
| CafeTran Espresso | You've never met a CAT tool this clever!
Translate faster & easier, using a sophisticated CAT tool built by a translator / developer.
Accept jobs from clients who use Trados, MemoQ, Wordfast & major CAT tools.
Download and start using CafeTran Espresso -- for free
Buy now! » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |