Pages in topic:   [1 2] >
Stopwatch in Excel
Thread poster: Ilka Nahmmacher
Ilka Nahmmacher
Ilka Nahmmacher  Identity Verified
Germany
Local time: 22:38
German to English
Jan 4, 2015

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


 
FarkasAndras
FarkasAndras  Identity Verified
Local time: 22:38
English to Hungarian
+ ...
? Jan 4, 2015

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
Dan Lucas  Identity Verified
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
Selcuk Akyuz  Identity Verified
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


 
FarkasAndras
FarkasAndras  Identity Verified
Local time: 22:38
English to Hungarian
+ ...
shortcut Jan 4, 2015

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.


 
Ilka Nahmmacher
Ilka Nahmmacher  Identity Verified
Germany
Local time: 22:38
German to English
TOPIC STARTER
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
Rolf Keller
Germany
Local time: 22:38
English to German
Learn VBA Jan 5, 2015

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
Ben Senior  Identity Verified
Germany
Local time: 22:38
German to English
Excel? Jan 5, 2015

@ Ilka,

Why do you say the timer should be in Excel? Would a standalone app serve your purpose?

Ben


 
Emma Goldsmith
Emma Goldsmith  Identity Verified
Spain
Local time: 22:38
Member (2004)
Spanish to English
Time Stamp Jan 5, 2015

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.


 
Ilka Nahmmacher
Ilka Nahmmacher  Identity Verified
Germany
Local time: 22:38
German to English
TOPIC STARTER
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


 
Ilka Nahmmacher
Ilka Nahmmacher  Identity Verified
Germany
Local time: 22:38
German to English
TOPIC STARTER
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
Ben Senior  Identity Verified
Germany
Local time: 22:38
German to English
VBA Macro Jan 5, 2015

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
Emma Goldsmith  Identity Verified
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
Ben Senior  Identity Verified
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


 
Ilka Nahmmacher
Ilka Nahmmacher  Identity Verified
Germany
Local time: 22:38
German to English
TOPIC STARTER
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 »