Excel realtime activity tracking

"Programm" isn't an English word. What would you like it to be? Do you need that heading at all?

Also your font sizes in the rest are 11. Would you like them to be 14?

"Programm" isn't an English word. What would you like it to be? Do you need that heading at all?

thanks , indeed that should have been program.  It can be renamed as  "Time Management" Heading.

Yes those font in my sample are really small,  you can use 14 font.

Not sure what size it would look till  you paste a font sample

Okay, finally, here's a partial solution.

• Click in column D (that has a description in column C) to start, stop or add a timer.
• Clicking Disable All Timers turns them all off.

1. How do you propose to use columns A and B?
2. What is the difference in functionality between a Timer and a Stopwatch?
3. What should the 'Total times' column show?
4. What is the difference between 'Task Description' and 'Task Title'?

29249392.xlsm

1. What should the 'Total times' column show?

Total times would show the total of  the Task Description.

Say if I studied excel  thrice at different times of the day,  then the Total times column would show total time I studied excel for that day by adding the time information from Column H onto Total Times  J

1. What is the difference between 'Task Description' and 'Task Title'?

Task Description is the detail of a task, while Task title is a cue  for the Total times to pick up the title information.

While Studied Excel thrice would be recorded in the Task description 3 times,  under the Task Title the word Excel would be shown only once and next to the word excel (column i), the total time will be recorded in Column J

Definitely need Column A  to record the dates.  But how do I go to 2 Nov 22.  Is it just pressing enter ?

The easiest thing to do would to just go to row 9 or 10 and just type it in.

Column B could be deleted.

I will do that but there will be a bunch of code I'll need to adjust.

As I could borrow the time information from Column F.

Did you mean column G, since F is the start times?

Timer needed for when I am doing time based task,  which can then be a pop up dialog to inform the time is finished.

OK.

Task Description is the detail of a task, while Task title is a cue  for the Total times to pick up the title information.

While Studied Excel thrice would be recorded in the Task description 3 times,  under the Task Title the word Excel would be shown only once and next to the word excel (column i), the total time will be recorded in Column J

I don't understand that but since I'm deleting column B, it would greatly reduce the coding changes I would otherwise need to make if column B could be 'Task Title'.

I don't understand that but since I'm deleting column B, it would greatly reduce the coding changes I would otherwise need to make if column B could be 'Task Title'.

Yes you can move task title to B column

The easiest thing to do would to just go to row 9 or 10 and just type it in.
ok , if you add a suffix 22 to the date please in Column A 

Did you mean column G, since F is the start times?

yes

I assume that Total Times (or IMO Total Time is better) is by day. True?

But you could add a total times in the last cell for that day

I would prefer the first cell for the day. If done that way then you would always know that the total would be in the same row as the date. Please let me know which way you choose.

Task Description is the detail of a task, while Task title is a cue for the Total times to pick up the title information.

I don't understand what you mean. Does it have something to do with a stopwatch? But talking about stopwatches I may not be understanding the difference, but it seems to me that the only difference between a timer and a stopwatch is that with a stopwatch you want a notification after a certain number of minutes. If that's the case then why don't we change column E's heading to 'Notify' and in that column you would enter a number of minutes for those tasks about which you want to be notified. For example you might have a task called 'Take pictures for item locater" and a value of 30 in column E. In that situation you would get a "Task 'Take pictures for item locater' duration has ended" popup 30 minutes after you start the task. I believe that in that scenario we could delete or hide column B.

If I make the font size slightly larger using the front group will that disturb the coding ?

No, not at all although the current font size is 14 which apparently you found acceptable in your other workbooks.

Also how do I delete the current time data in Column F and G...

1. Select a cell in column F and drag the cursor to column G
2. Right-click and choose 'Clear Contents'

Note that that you can do more than one row at a time and it doesn't need to be limited to F and G. For example you could select cell F5, drag the cursor to G10 and then 'Clear Contents' would delete all of that. You can also delete whole rows by selecting one or more row numbers, right-clicking and choosing 'Delete'.

I would prefer the first cell for the day. If done that way then you would always know that the total would be in the same row as the date. 

Agreed

while Task title is a cue for the Total times to pick up the title information.

What I was meaning was, if you look at row 6 and row7 , it says study excel twice under "Description"

So for the code to detect the title of the description,  it has to look for the key word in the Description.

which is Excel in this case and not Study Excel.   Hence the the code in J column will look for the keyword in excel and add the two times that I studied excel and place it in  J3 which is the total time of a task title.

If I say in the Task Description for example " Searching for  the bluetooth adapter"

The keyword for the Total time here is  "Searching"  

So only when I type the Task title ,  the J column will then look for that keyword searching in the Task description.

and if I have searched for different items 3 to 4 times a day, then the Total times column will add those 3 to 4times to give a total time of the keyword  "searching" 

In the above example, I have manually calculated the number of times I studied in excel and added 3 min and 6 secs.(i.e. 2.52 + 14 sec = 3 min 6 secs) and the second example keyword is internet , I added the internet times manually to display 3 min and 15 secs

 

If that's the case then why don't we change column E's heading to 'Notify' and in that column you would enter a number of minutes for those tasks about which you want to be notified. 

ok I am happy with this suggestion.

I believe that in that scenario we could delete or hide column B.

So how would the Total time code look for keyword in the Task description if the Task title column is deleted.?

The task title was the keyword source for the J column

Okay, I agree that deleting or hiding B isn't a good idea.

This is very difficult and I've been obsessed with getting it right (to the detriment of the problem in your Delinking MG Tab question), but give this a try. Start by clicking the new Add Day button.

This workbook does not yet do anything with stopwatches, and talking about that I have a couple of questions:

• You can't do two things at the same time so why have more that one Study Excel task description?
• Why can't a value 15 be the trigger to notify you that a given item has been running for 15 minutes? If we do that then Task Title can be eliminated or ignored.

29249392a.xlsm

• You can't do two things at the same time so why have more that one Study Excel task description?

You are right, but there are instances where I multi-task too.  

But in the case of study I could not study excel for 4 hours straight.

I might study Excel  in the morning from 11.30am to 11.50 am

Again I might be motivated to study excel in the Afternoon 2.30 to 3.15pm

and a third time I might study excel 7pm to 8.15 pm .

So I have studied excel 3 times a day at different hours.

11,30 am to 11.50 am

2.30 pm to 3.15 pm

7pm to 8.15.

So therefore I mention that Excel study in the Task Description and the From and To times are recorded against those.  

• Why can't a value 15 be the trigger to notify you that a given item has been running for 15 minutes? If we do that then Task Title can be eliminated or ignored.

So how the Total Times find the keyword ?  Task title is the source of the keyword.

Maybe I could you give you an alternative suggestion if you do want to eliminate the Task Title.

I will have to bold the keyword in the Task Description  and the Total times coding could pick the keyword  from the bolded word in the  Task Description, then that way the Task title may not be necessary.

Is it possible for the code to recognise the bolding of the word in the Task Description ?

Regarding NOTIFY,

Can I not as a user decide what value to add in the notify Column for the relevant row ?

Can I not as a user decide what value to add in the notify Column for the relevant row ?

Certainly you can; 15 was just an example.

Let's assume you have the three Study Excel tasks in rows 3, 5 and 8. Are you saying that you want to be notified when the total time spent on those three tasks reaches, for example, 90 minutes? If so which row would contain "90"?

Is it possible for the code to recognise the bolding of the word in the Task Description ?

No, because it's not possible for you to bold part of a cell.

Let me mention that all the code that tracks the time spent in each task is based on a single Excel timer function that currently runs once each second, and so if we add much more code to the time like new code to implement you may see a lag in the To times and Duration for the tasks. Personally I find it a little annoying to deal with the constantly blinking cursor, so I propose the timer be change so that it runs perhaps every 5 or 10 or some other number of seconds.

Let's assume you have the three Study Excel tasks in rows 3, 5 and 8. Are you saying that you want to be notified when the total time spent on those three tasks reaches, for example, 90 minutes? If so which row would contain "90"? 

I will notify my self in Row 3 for example in 30 min,  in Row 5  because I am motivated, I could notify myself in 50 min.,  but in the evening I might notify myself for just 10 minutes as I would be bored for the day.

So notify is not for total of 3 rows, ,but it is for individual rows .

Personally I find it a little annoying to deal with the constantly blinking

Yes I saw that and agree with you on that, good you noticed it.

No, because it's not possible for you to bold part of a cell.

In that case the task title is required.

So notify is not for total of 3 rows, ,but it is for individual rows

Then in the scenario we've been talking about the three values in Notify is all that's needed.

hen in the scenario we've been talking about the three values in Notify is all that's needed.

did not follow what you meant ?

What I mean is that task Title isn't needed. Give me a while to put something together and if you don't like it I can undo it.

I think that I will need to turn the particular task off when its notify popup is triggered, otherwise there could be several popups shoeing at the same time. Is that okay?

What I mean is that task Title isn't needed. Give me a while to put something together and if you don't like it I can undo it.

ok

Wait, try this.
Talk.xlsm

I think that I will need to turn the particular task off when its notify popup is triggered, otherwise there could be several popups shoeing at the same time. Is that okay? 

 I would notify usually  for the current task that I am doing.

yes I clicked on that  and seems ok, it is visual plus sound. I am happy with that, but where would it be placed as that pop up looks big.  Meaning it would not cover or block the viewing of  the data area ?

If the visual you are talking about says "Click me" then maybe I got too cute in that that is only a button which triggers the speech and there won't be any visual in your workbook.

 there won't be any visual in your workbook.  

I thought there would be pop as well if it was possible.

Popup:

1. Need to be in front of the PC to see it
2. Clutters the screen

Speech:

1. Only need to be near the PC to hear it and it will play every few seconds until the task is turned off
2. No clutter

I don't think both are needed and I think it's obvious that I'm voting for speech.

I think it's obvious that I'm voting for speech.

will agree on that for now.

Well I tried that and it doesn't work very well since the speech takes a few seconds and during that time you get the "busy" cursor and you can't do anything, so I went with a visual approach. The times in the Notify column are minutes so .2 is 12 seconds. I did that for testing purposes. I wouldn't expect you to have tasks anywhere near ,2 (or the others) but I used .2 as a test to show that you could, if you want, have fractional minutes. Unless you tell me I shouldn't, the next time I touch this workbook I will make it so that entries in that column round to whole numbers.  

I changed the timer interval to 5 seconds, and while this won't make sense until you try the workbook, the red color will go away when you turn a task off.
29249392b.xlsm

when you turn a task off.

After the notify  reached its time and the color changed to red.

I tried to switch off the timer with the mouse , but it would not switch off.

Make sure that the cell in the Timer column is not already selected. If it is click somewhere else that's not in that column and then click the timer you want to turn off again.

O

since I cannot switch off directly and I go out to another cell and come back again then there is popup, so which cell should I come out to ?

Make sure that the cell in the Timer column is not already selected. If it is click somewhere else that's not in that column and then click the timer you want to turn off again.

No it does not switch off

Please run the video.
2022-11-15_14-57-29.mp4

ok I have uploaded a recording and you can see yourself that it is not happening.

Also the hourglass seems to be constantly on.  can the hourglass be eliminated please ?

time not stopping.gif

can cells in Timer Column D be linked to keyboard shortcut - " Spacebar ". as the easiest way to stop the timer ?

See if the new button helps. It may not be needed since I changed the timer to fire every minute.
29249392c.xlsm

See if the new button helps

The button helped.

I changed the timer to fire every minute.

Now  how to know the stopwatch is working since I can't see the digits change every second like I used to ?

And What is preventing the digits to tick every second, I mean can the busy cursor be out of view or some way to keep the busy cursor off. ?

Also can the keyboard shortcut  " spacebar " be coded to stop the STOPWATCH ?

The "Timer" is a built in function of Visual Basic for Applications (VBA) which is the language I'm using to create the code that runs the functions of this and your other workbooks.

When a timer is turned on it sits in the background ticking off the seconds as they go by based on the PC's internal clock. You can have multiple timers but in this workbook there is just one. That may be a little confusing because in column D it looks like there are several timers, but it's really just one, and the "timers" that you see are just text values that tell the real timer to do things in the row when the cell says "On" and to ignore the row for the most part (see #3) when it says "Off".

A timer can be used in several ways and in this workbook I'm telling the timer to do something every time a set amount of time goes by. In other words when it should “Fire”. That "something" is:

1. For the rows that are "On”, place the current time in column G which is very quick
2. For the rows that are "On”, calculate the difference between column G and column F and place the result in column H formatted in hours, minutes and seconds which is not so quick
3. Calculate the total for all rows (including those that are “Off”) and place the result in column ‘I’ formatted in hours, minutes and seconds. What that means is that if there are for example four tasks, what is done in #2 is essentially done four times and that is relatively slow.

All that takes time, and while the calculations are being done VBA puts up the ‘busy’ cursor and you can’t do anything until it goes away. In the initial version of this workbook I set the timer to fire every second but that turned out to be much to quick and so in the ‘b’ version I changed it to fire every 10 seconds, but while that worked for me, it didn’t for you, so in the ‘c’ version I changed it to fire every 60 seconds.

Does the wait cursor still get in your way?

Now  how to know the stopwatch is working...

I can probably give you a visual indication that the stopwatch (which you now know is the timer) is running. Let me know that you want that.

...can the busy cursor be out of view or some way to keep the busy cursor off. ?

Sorry, no.

Also can the keyboard shortcut  " spacebar " be coded to stop the STOPWATCH ?

When you say "spacebar" do you mean the Enter key? If so how would the shortcut know which task to turn off?

When you say "spacebar" do you mean the Enter key

Not the Enter key, but the spacebar key

In below picture , you see the long blue color key  on the keyboard is the spacebar key

If so how would the shortcut know which task to turn off?

When the task description is typed and the enter key is pressed, the cursor jumps to the relevant cell in the D column switches the  timer on and stays on that column.

So now when I am ready to off the stopwatch,  since the cursor is already in the cell of D column, pressing the enter key should switch it off.

I can probably give you a visual indication that the stopwatch (which you now know is the timer) is running. Let me know that you want that.

Yes that is what I saying in the previous comment was if you could pop up a time duration button which could be ressized by the user

Does the wait cursor still get in your way?

No

Calculate the total for all rows (including those that are “Off”) and place the result in column ‘I’ formatted in hours, minutes and seconds.

The total times I column was for calculating the sum of the 3 times duration of excel study as an example,

but not the total of all the times.

Well I certainly misunderstood that requirement and I spent a lot of time doing it the wrong way:( When we talked about where to put the total time I suggested (based on my misunderstanding of the requirement) that it should go in the first row and you agreed so I never guessed that I was doing it wrong.

I usually always say I do not understand,  sorry not sure why I missed that.  However I did keep mentioning in some comments that  the sum of 3 excel study times will be equal to the total times for Excel study only and not the total of all times.

.

..if you could pop up a time duration button which could be ressized by the user

I can't do any popups in this workbook. Remembering that there is just one timer in the workbook, here is what I propose an alternate solution. If the timer is shut off it would says "Timer is off" and have a gray background.

This time I can say I did not understand . Is that the heading of the D column or ? or if you can take full snapshot ?

ok now I understand so it is the H column 

that should do.  I will have to view it as an open workbook to have a feel of how it looks

I'm going to take a couple of days rest before I get to it.

ok 

Okay I'm back at it. For a technical reason I need to set a limit on the number of tasks (Task Description). Please give me a reasonable number limit for a day.

Maybe 10 tasks for now.   Hopefully you will be able to extend task limits in the future.

I'm having difficulty summing the task times for tasks with a Task Title. I believe somewhere here that I suggested having just one task title for things like "study excel" but you rejected that, but how about this as a compromise? Normally when you turn a task "On", the from time changes to the current time but I could make it so that if there's a task title for the task that that doesn't happen. So the net effect would be to show the total time studying excel even if you stopped that task during the morning and started it up again in the afternoon.

So the net effect would be to show the total time studying excel even if you stopped that task during the morning and started it up again in the afternoon. 

Sort of followed what you said, but would be able to better understand once you upload a sample test.

Also I have asked a new question for  Lotto tweaks.

In the attached workbook I:

• Changed some button captions so as to make clearer what they do
• Rounded Notify values to whole numbers
• Added timer is on/off indicator in cell H1
• Set Duration to zero when a task is turned on
• When a cell in column D is clicked it no longer shows as selected which makes it easy to change again. I temporarily hid the 'Stop Selected Task' button because I don't believe it's needed any longer. I'll unhide it if you find that you still need it.
• Added a 10 task limit per day. The 10 can easily be changed to something else.

You requested a shortcut but after entering the task description no shortcut is needed to select the Status cell for that row; just press the Tab key

To start testing enter a title in B2 (it could be any any cell in that column that has a status and the text can be anything) and turn that task and any other tasks on.

I just realized that column 'I' isn't necessary since the 'I' value for "Study Excel-type" tasks will now always equal the Duration. If you agree I'll remove that column the next time.


29249392d.xlsm

thanks will check that now.

I have also asked a new question  for Lotto Tweaks  "Arrow across..............

To start testing enter a title in B2 (it could be any any cell in that column that has a status and the text can be anything) and turn that task and any other tasks on.

I have not understood the explanation you have provided.

So I have typed the task title in B column " Driving to get bread" and pressed enter, but the cursor jumps directly to column I3.  But the timer did not start in D, so is the timer silently on to prevent the busy cursor. ?

Also I have not under understood the cell H1 function, because I see the H1 green cell Timer is on but I don't see it to anything visually.

You requested a shortcut but after entering the task description no shortcut is needed to select the Status cell for that row; just press the Tab key

I understood this one , and the tab key worked to off the task description

So I have typed the task title in B column " Driving to get bread" and pressed enter, but the cursor jumps directly to column I3.

That doesn't hurt anything but there is no need to press Enter.

Also I have not under understood the cell H1 function

Press 'Stop All Tasks' and notice what happens.

Press 'Stop All Tasks' and notice what happens. 

Understood. it grays to indicate that it is stopped.

In below picture , if you see the duration column G should have been 4 min, but the duration still shows Zero.

So I do need to see the duration of the spent time which was approx 4 minutes.

Also I typed 2 in Notify column but it did not do anything.  How does the notify work here ?

So I do need to see the duration of the spent time which was approx 4 minutes.

Are you asking me to do something?

Also I typed 2 in Notify column but it did not do anything.  How does the notify work here ?

After typing a value in the Notify column, click the Status cell for that row. After the Notify number of minutes is reached, the Duration cell for that row will turn red. If you click the Status cell again the red will go away.

I made a small unrelated change to the code.
29249392d.xlsm

No that wouldn't stop the timer.

As I said about the red, "If you click the Status cell again [in the row that has the red], the red will go away".

ok will test it out again with 1 minute notify.

If I am not doing it correctly then if you could kindly attach a video, which could show after clicking the status cell that it is continuing to log the duration.

I did not envision that you would add a Notification to a stopwatch type task, and to me it doesn't make sense to do that. My understanding of a stopwatch type task is one that you would start, work on it for a while, turn it off, and then perhaps come back to it later in the day so I don't see the need to set a Notification. Add a Notification to a regular task to see how it works.

I did not envision that you would add a Notification to a stopwatch type task, 

That usually goes for a Timer Type task which I had hoped you would be able to add and I had explained that in the following link of a previous comment

Link to     Timer and Stopwatch  comment

But that timer option which was for time based task did not materialize and you coded for stopwatch instead,

so I don't see the need to set a Notification

And because there was no timer , I had to request a notification so that the stopwatch behaves like a timer by glowing the cell red.

What did you ask for that you can't do?

What did you ask for that you can't do

Sorry, did not understand your question.

Okay I must have misunderstood, but I thought that you were saying that something wasn't working properly or it was missing a feature that you asked for. If that's the case please let me know what it is.

Okay I must have misunderstood, but I thought that you were saying that something wasn't working properly 

Yes I am checking if I am doing the steps correctly

So when I open the workbook,  I automatically go to B column to type the task title,  but when I pressed enter the cursor jumped to H column.

So when I press enter in the B column , could you kindly let the cursor to jump to C column instead so I can type the task description.

Now because the cursor jumps to H column I have to bring it manually to C column to type Task description

There is no need to, and in this case you shouldn't, press enter. You should just type in what you want and then select where you want to go, however if your are in column B, pressing Tab when you are done typing will take you to column C.

however if your are in column B, pressing Tab when you are done typing will take you to column C.

Once I finished typing in Column B, pressing Tab does not take me to Column C,  but the cursor jumps from B straight to H.

What I was hoping to happen was

1) Type in Column B

2) After finishing typing in Column B, press tab

3) Pressing Tab should move the cursor to Column C where I type the task description.

4) Now either pressing the tab key or enter should trigger the STOPWATCH to ON position.

Once again it doesn't work like that for me. For me when I type in column C and then press tab it goes to column D.

What you should do is after typing in column C is to just select column D. That's actually easier then having to let go of the mouse and then having to press the Tab key.

For me when I type in column C and then press tab it goes to column D.

That works for me too which is what I wanted and fast

But what does not  happen is after typing in Column B, pressing the Tab key does not move the cursor to Column C.

If you could kindly let that happen, i.e. Typing in Column B and then press the Tab key to move the cursor to Column C

So forget about Tab and similar to moving form C to D, what you should do is after typing in column B is to just select column C.

I was hoping it was possible for you to facililate moving via tab key  from B to C,  but if is not then I will do as you suggested select column c

I can't.

ok. I will do as you suggested.

In above picture ,I cleared the previous dates and selected a new date , but the red under H column shows up even though the notify column is blank.

I was thinking it would should show up only when I type a value in notify column ?

Time Tracker

Cursor Jumping from B to  I  and also from C to I

So when I typed the Task Title  in B column ,  I clicked the mouse button in C column but the cursor jumped straight to I column.

So when I type in B column and use tab key it jumps to the I column anyway.

But now as you suggested , I typed in B column and clicked the mouse button in C column so I could type.

but as soon as I clicked the mouse button in C column, also the cursor jumps straight to I column.

Is it possible to fix this by the following steps.?

Type in B column,  then  press tab key, the cursor moves to C column.

This does it without needing the Tab key.
29249392e.xlsm

I forgot how to clear the red notify color in Column H,  so that I can continue the task ?

Is clearing or deleting rows something you expect to be doing in the future? If so please explain why you'd want to do either.

For now I suggest you delete all the rows starting at row 3 and start fresh.

I made it 10 because that was your answer to my "Please give me a reasonable number limit for a day" request, but I can make it 100 if you like if there is never more than one day present in the Tracking tab. What I envision is that you would enter and track all your tasks on day 1 and then on day 2 you archive day one's data (via a process created in a new question) and then enter track all your tasks on day 2, etc.

What I envision is that you would enter and track all your tasks on day 1 and then on day 2 you archive day one's data

Correct. that is what I would do