Pages in topic: [1 2] > | Stopwatch in Excel Thread poster: Ilka Nahmmacher
|
I am looking for a stopwatch function to integrate directly in Excel. I would like to use it to figure out exactly what I earn per hour when I translate by word or line. Requirements: 1) It should start, stop and resume the clock by clicking a button. 2) It should be able to store the value and be able to resume counting when the file is opened again. 3) Excel should be able to use the value in a calculation with the project price to figure out my hourly rate. ... See more I am looking for a stopwatch function to integrate directly in Excel. I would like to use it to figure out exactly what I earn per hour when I translate by word or line. Requirements: 1) It should start, stop and resume the clock by clicking a button. 2) It should be able to store the value and be able to resume counting when the file is opened again. 3) Excel should be able to use the value in a calculation with the project price to figure out my hourly rate. I know that it is possible to program macros with VBA to do this, but I don't know how. Can anyone supply a file in which this has already been done, or suggest an alternative method? I have looked at TimeStamp, Catchit, etc., and they are useful, but they don't calculate my hourly rate for me. I would like to automate this process as much as possible. Thanks. Ilka ▲ Collapse | | |
Are you sure that programming an excel stopwatch or convincing someone to do it for you will take less time and effort than entering the time values by hand? I'm all for laziness, impatiance and hubris in programming, but this might not be a worthy target for laziness. Note that Ctrl-Shift-semicolon inserts the current time in the active cell. You can probably have a start cell and an end cell, insert the relevant times with the keyboard shortcuts and write an excel formula that calculates... See more Are you sure that programming an excel stopwatch or convincing someone to do it for you will take less time and effort than entering the time values by hand? I'm all for laziness, impatiance and hubris in programming, but this might not be a worthy target for laziness. Note that Ctrl-Shift-semicolon inserts the current time in the active cell. You can probably have a start cell and an end cell, insert the relevant times with the keyboard shortcuts and write an excel formula that calculates the time elapsed between the two. ▲ Collapse | | | Dan Lucas United Kingdom Local time: 21:38 Member (2014) Japanese to English Can't think of a way round this | Jan 4, 2015 |
Ilka Nahmmacher wrote: I know that it is possible to program macros with VBA to do this, but I don't know how. Can anyone supply a file in which this has already been done, or suggest an alternative method? For this level of sophistication I think VBA or some other programmatic manipulation is unavoidable. Excel provides many hook functions that can be used, such as Workbook_Open(), but relying on this and Workbook_Close() might be risky. Dan | | | Selcuk Akyuz Türkiye Local time: 00:38 English to Turkish + ... ctrl+shift+period | Jan 4, 2015 |
FarkasAndras wrote: Note that Ctrl-Shift-semicolon inserts the current time in the active cell. Hi Farkas, AFAIK Ctrl+Shift+semicolon inserts the date and Ctrl+Shift+period (full stop) inserts the current time. Selcuk | |
|
|
Selcuk Akyuz wrote: FarkasAndras wrote: Note that Ctrl-Shift-semicolon inserts the current time in the active cell. Hi Farkas, AFAIK Ctrl+Shift+semicolon inserts the date and Ctrl+Shift+period (full stop) inserts the current time. Selcuk Microsoft says otherwise: https://support.office.com/en-nz/article/Insert-the-current-date-and-time-in-a-cell-02e29e25-93b2-4799-b707-3a9e0ccefd46 I can't confirm or deny as my Hungarian Excel has different shortcuts than most other versions (we use the comma as the decimal separator and that messes with things). Not that it matters either way, the point is that there is an easy shortcut. | | | Response to Farkas: worth a try | Jan 5, 2015 |
Farkas, it could turn out that programming a stop watch and hourly rate calculator is very involved. But once done, I'd have it for years to come. And since I often have three or four jobs a day, it seems worth a try anyway. Thanks for the info on entering the current time into Excel. I didn't know that. | | | Rolf Keller Germany Local time: 22:38 English to German
Ilka Nahmmacher wrote: But once done, I'd have it for years to come. Right. Some of my macros are 20 years old. But most ready-to-use macros don't fit exactly to your needs, so you'd better try to learn VBA. The knowhow will pay off under many different scenarios. It's just a rather simple foreign langugage, and there are lots of tutorials in the web and in printed form. | | | Ben Senior Germany Local time: 22:38 German to English
@ Ilka, Why do you say the timer should be in Excel? Would a standalone app serve your purpose? Ben | |
|
|
Emma Goldsmith Spain Local time: 22:38 Member (2004) Spanish to English
Ilka Nahmmacher wrote: I have looked at TimeStamp, Catchit, etc., and they are useful, but they don't calculate my hourly rate for me. Time Stamp does calculate your hourly rate. That's exactly what I use it for. | | | Where to you enter the project price? | Jan 5, 2015 |
Dear Emma, I didn't see that feature. Where do you enter the project price? I see where I can enter my hourly rate, and then it calculates how much I earn for a certain number of hours. I want it to do the reverse: take my project price, divide it by the hours I spent, and yield a rate per hour. Thanks. Ilka | | | Response to Ben: Why standalone is insufficient | Jan 5, 2015 |
Ben Senior wrote: @ Ilka, Why do you say the timer should be in Excel? Would a standalone app serve your purpose? Ben Hi Ben. Yes, I could easily use Timestamp, for example. I would have to keep a list of projects there, since I often work on multiple projects at the same time. But I already enter my projects in an Excel worksheet with all of their data, so that would mean duplicate work. Having the timer in Excel would also save me the step of having to copy over the times into Excel. You see, once I have my rates in Excel, I can sort the projects by customer, agency, type of text, TMS, etc., to generate some useful statistics. Basically, anything that makes this task more time-saving makes it more likely I'll actually do it! | | | Ben Senior Germany Local time: 22:38 German to English
Hi Ilka, If you are still interested I now have a macro that should do what you want, but I need a little feedback from you first. I have assumed that your spreadsheet is arranged with the separate projects all on their own rows and various information to each project in various columns of that row. Correct? If not I'll need to re-jig the macro. The whole thing is governed by two macros, one to start the timer and one to stop the timer. You will need to hav... See more Hi Ilka, If you are still interested I now have a macro that should do what you want, but I need a little feedback from you first. I have assumed that your spreadsheet is arranged with the separate projects all on their own rows and various information to each project in various columns of that row. Correct? If not I'll need to re-jig the macro. The whole thing is governed by two macros, one to start the timer and one to stop the timer. You will need to have two buttons on your QAT to run the VBA code. Can you do this or will you need a little help? The macro requires three columns which must be adjacent to each other and in a specific order. Their names and order are "Project price", "Accumulated time" and "Hourly rate. They don't need to have these names you can call them what you want, but the order is important. The macro requires three other cells for storing its data but they are way, way off to the right and should not interfere with your spreadsheet. I could move them nearer to your data if you wished. How the macro works: You select in your spreadsheet the cell "Accumulated time" for the project that you will be working on. You click on the "Start timer" button and the background of the cell changes to green to show you you have started the timer. You then work on your project and when you have finished for that session click on the "Stop timer" button. The macro then changes the background of the cell to red to show you have stopped timing that project. It then calculates the time you've been working on that project and adds the time to the value in the "Accumulated time" cell and calculates and displays the current hourly rate. In any one day you can time as many projects as you wish, there is no limit. Would such a macro be of use to you? If so I will finish it off. If you want it to do things a little differently just let me know. Regards Ben ▲ Collapse | |
|
|
Emma Goldsmith Spain Local time: 22:38 Member (2004) Spanish to English No, not project price | Jan 5, 2015 |
Ilka Nahmmacher wrote: I see where I can enter my hourly rate, and then it calculates how much I earn for a certain number of hours. I want it to do the reverse: take my project price, divide it by the hours I spent, and yield a rate per hour. Ah, I understood an hourly rate, not a project price. A quick division wouldn't take long, though. Never mind, it looks like Ben is coming up with a tailormade solution for you. | | | Ben Senior Germany Local time: 22:38 German to English Ilka: Which Excel are you using? | Jan 5, 2015 |
Hi Ilka, Just a couple of quick questions. Which version of Excel are you using, 2013, 2010...? Is it the German language version? I'm out of the office all day tomorrow with a client so you probably not hear from me again until Wednesday. Regards Ben | | | Answer to Ben's questions | Jan 5, 2015 |
Dear Ben, I am interested. To answer your questions: 1) My spreadsheet is arranged like you say. 2) I do not know how to place two buttons on my QAT to run the VBA code 3) Your macro does exactly what I want it to do. I assume I can continue accumulating project time on another day after closing and opening the Excel file? 4) I still use Excel 2003. Can you believe I've gotten by with that for so long. 5) Excel is the German language version. ... See more Dear Ben, I am interested. To answer your questions: 1) My spreadsheet is arranged like you say. 2) I do not know how to place two buttons on my QAT to run the VBA code 3) Your macro does exactly what I want it to do. I assume I can continue accumulating project time on another day after closing and opening the Excel file? 4) I still use Excel 2003. Can you believe I've gotten by with that for so long. 5) Excel is the German language version. Are these macros you use for your own purposes, or ones you just wrote because you loving writing macros? Thanks. Ilka ▲ Collapse | | | 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 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 » |
| Trados Business Manager Lite | Create customer quotes and invoices from within Trados Studio
Trados Business Manager Lite helps to simplify and speed up some of the daily tasks, such as invoicing and reporting, associated with running your freelance translation business.
More info » |
|
| | | | X Sign in to your ProZ.com account... | | | | | |