Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Timer

0

Hello everyone, I hope this meets y'all well.

So, I am building an excel sheet to help my brother time occupants of his lounge and then convert the duration to money.

I was able to add a macro for start and stop from the tutorials on this platform(thank you so much) but I don't know how to create a macro to reset the timer so I can attach a button to this.

And, please how can I convert the value of the cell to minutes so I can multiply by the rates and get the total cost?

Thank you so much in anticipation.

Answer
Discuss

Discussion

Whow Josh. You got a lot of problems. Let me help you with a few of them.
(1) Observe forum rules: don't ask more than one question in a thread. (2) If you want code modified, post the code you have. (3) If you want to know how to handle time in Excel, first read up about how Excel handles time.
Variatus (rep: 4889) Aug 6, '20 at 8:14 pm
Add to Discussion

Answers

0

If you are talking about this time macro then just make another button and attach it to a new macro that calls the stop timer, resets the cell, and calls the start timer, like this:

sub MyMacro()
call stop_timer
range("A1").value = "00:05:00"
call timer
end sub

I just typed this out by hand but it should get you started.

For the other part of your question, just post a new question and sample data or a sample file and it will be much easier to help you :)

Discuss
0

 You didn't include a sample file so I'm taking a guess that the page layout is something like this:

Row 1 is a header row withcolumns "A" being "Name", "B" is "Arrival Time", "C" is Leave Time", "D" is "Duration Time, "E" is "Rate per Minute", and "F" is "Charge"

Data for "A","B", "C", and "E" are keyed in; "D" and "F" are calculated using formulas.These can be worksheet formulas or VBA. If you opt for VBA you wil also need to create variables. "Arrival" and "Leave Time" must be entered with Am/PM or using the24hr clock (military time, ie 3:48 PM is 15:48)

Worksheet formulas: for the "Duration Time": =IF(C2>B2, C2-B2, 1-B2+C2).  For "Charge": =(HOUR(D2)*60 + MINUTE(D2)) * E2. {copy down the sheet for as many rows as needed}

Using VBA: (calculations done in minutes) for the "Duration Time":If sTmin < eTmin Then dTmin = eTmin - sTmin Else dTmin = 1440 - sTmin + eTmin. For "Charge": Range("F2") = dTime * Range("E1").Value; {variables used: sTmin = arrival, eTmin is leave, dTmin = duration in minutes. If using multiple rows (one for each guest) you will need to incorporate a loop to cycle through each row containing data. To reset the "Arrival" and "Leave" times you could use code something like: Range("B1:C1").ClearContents

I have attached a file with both macro and worksheet examples.

Hope this helps.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login