trying to create a button that calculates time spent on a task

[CalamL]

Hi,

A quick introduction:

I am a high school teacher with no background in excel except my fascination for it.

I am trying to create a spreadsheet with analytics for my marking progress to help me churn through piles of exams and assignments.

So far I have created a button that removes 1 from the column of 'left to grade' and adds 1 to the column for 'completed'. I added a graph and it helps keeps me motivated during the hours and hours of marking.

I am trying to create a button that adds the current time to a cell and then moves on to add the current time to the next cell (.e.g click the button and the current times gets input in A2. Click button again and the current time gets added to B2 (column c will do a2-a1 to work out time spent on that paper). Click the button again and the current time gets added to A3, then B3, A4, B4 and so on).

I've tried figuring it out myself from basic tutorials and it seems there's a few possible ways it can be done (Using if then, elseif, ISBLANK and >0 (supposedly very easily), but I keep getting lots of errors.

Please don't laugh at me too hard, I have no background in coding and I assume the solution will probably be much more complex than I was anticipating.


Range("A2").Select
If ("A2") ISBLANK
ActiveCell.FormulaR1C1 = "=Now()"
ElseIf Selection.Offset(1, 0) Then
ActiveCell.FormulaR1C1 = "=Now()"
ElseIf Selection.Column < 2 Then
Selection.Offset(-1, -1).Select
ActiveCell.FormulaR1C1 = "=Now()"
ElseIf Selection.Offset(1, 0) Then
ActiveCell.FormulaR1C1 = "=Now()"
ElseIf Selection.Column < 2 Then
Selection.Offset(-1, -1).Select

End

[jmac1947]

Hi CalamL and welcome to the forum.

I am sure over time you will get much information & assistance from the many volunteers who contribute tot he discussion threads.

There are of course "forum rules" to follow and I am confident one of the moderators will update you these.

One of the key things to always remember is that attaching an example spreadsheet will pretty much guarantee that you get a quicker response and possible solution. Most of us are "time poor" (something a teacher would understand I suspect ) and don't have the time to generate a spreadsheet to test suggestions on before we reply. One of the good things but also equally a bad thing is that there are many ways to deal with a question in excel, some better that others but provided you get the correct outcome almost any solution will do.

the example doesn't need to be big, it just needs to show the inputs you will be working with and the expected outcome of the process.

Look forward to seeing your current progress and, hopefully, being able to assist.

[Logit]

.
One suggestion. The format can be changed around to suit your needs.

Please Login or Register  to view this content.

Double-click in the appropriate cell Row 2 and Row 3 for begin / end times. Total time auto displayed from formula in Row 4.

[CalamL]

Thanks for the info jmac. I mistakenly thought that the experts here would just bang it out from their keyboard.

I look forward to learning a lot. I enjoy tooling around occasionally but am very basic. I once made a calculator for levelling up in MW2 that had a few bits of data and calculations in it. I thought it was great but it was very basic haha.


Thanks for the response Logit!

I'll upload a copy of my spreadsheet now so you can get a better idea of what it is and what I need.

I want my single button at the top to continue counting up and down for me, as well as adding the current time into the "time started" and "time stopped" cells.

I also just realised that the time started can be taken from the time stopped of the previous job and considering that I only want to have to click the button once when I finish marking an assignment, I cant do a click for both start and stop so will need to workout a trick for that :/

[jmac1947]

I also just realised that the time started can be taken from the time stopped of the previous job :/

What happened to a good old fashioned coffee break between papers???

[CalamL]

Haha. The main issue is that if I press it twice it will add 2 to my done pile. I wish it was that easy to knock them over

[Logit]

.
Paste this into your macro. Make certain the first time you run the macro, you have selected B7 as your active cell.

Please Login or Register  to view this content.

[CalamL]

Thank you! It solved my problem and I will learn from it.

You're a legend. Hope you feel good

[Logit]

You are welcome.