• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help with macro for dynamically track elapsed time with 5minute visual alarms

B

becha

New Member
hello all of you experienced users of excel 07 and vba coding

im from mexico. i apologize in advance for my poor english translation


i was wondering if anyone of you could help me in providing vba coding to automatically track elapsed time. coding should be able to allow excel to display visual cues when a certain minute threshold is met.


here is what i need

in one sheet under column D i would type in the time (00:00:00 24hr format)


under column E i would type the amount of time targeted to complete a task (say either 30 mins or 60 mins using 00:00:00 24hr format)


on subsequent column F, G and H i would need that without any user intervention a vba code dynamically tracks the time elapsed between:

the sum of (the time input in column D plus the mins target typed in column E) and the current time internally being tracked by the code


so that in column F the vba code displays a visual cue such as a yellow colored background cell via cond formatting when the current time is 5 minutes before the previously referred SUM.


in column G a red colored background appears when the current time is equal to the aforementioned SUM


and finally in column H a green colored background cell appears when the current time is equal to the SUM plus an additional 5 minutes


my deepest gratitude to anyone of you who are willing to lend a hand

saludos
 
Hi, becha!

And should Excel know that the task is completed? And all three color signals shouldn't be in the same cell?

Regards!
 
mr excel ninja SirB7.


first: thank you for rendering your service. i humbly thank you.!!!

i sincerely hope i dont confuse you wth further narrative:


after posting i starte to think more about some details.


i plan to use the sheet as a tracking log for many activities.

so any subsequent rows will have times and targets of different employees


so the vba code would 'know' the task is completed when in cell under column L a user types as permanent data the deadline time. theoritically such time stamp will be equal to the SUM of the time when the task was initiated plus the targeted time plus 5 more minutes.


when vba detects cell under column L is nonblank (or there exists a time stamp) then the code should treat the records on that row as permanent. that means leaving the cells colored in columns F thru H


as soon as the following row has data entered in column D and in E, the vba code should start tracking time and displyaing visual cues as original post


and so on and so on


overall i think the sheet might be better to have a button to start the code running. and another one to end the code from running.

so that at begining and end of working day the user decides when to turn on and off the running of the vba code


finally, i need the three different separate cells because after the first threshold is met (column F: 5mins before deadline) i would like the cell left colored so as to leave evidence that the alarm went off

then after the current time is equal to deadline the following cell turns red and it stays that way

finally when 5mins after deadline the green cell will be displayed and left as is


let me know if i can shed more clarification

thanks again sir!
 
Hi, becha!

I was wondering about one cell only as the Application.OnTime method would be used. Neither VBA (nor VB too) never felt fully comfortable with timers (VBA doesn't have), so if you're planning to trigger three events per line added to the macro for the change event, I definitively suggest you to forget about it, becoming unusable proportionally to the number of entries. All this IMHO.

Regards!
 
thank you Sir

Ok. lets go with one cell displaying a yellow backgroudn color, only for it being overrriden after with a red one and then finally overridden by a green one that should remain visible after the user types in data in cell under column L


with that amendment to my original idea, do you think its possible to have the vba code?
 
sorry. column J cells would now be the ones in which the user finally enters a time stamp.

column L wont be needed as we remove the visual cues off columns G and H.

however i do plan to use columns G thru I to type other user data.


let me know sir!


my gratitude to you
 
SirJB7....is there any addtl info you need to make the vba coding?

good evening. thanks in advance
 
Hi, becha!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/help%20with%20macro%20for%20dynamically%20track%20elapsed%20time%20with%205minute%20visual%20alarms%20%28for%20becha%20at%20chandoo.org%29.xlsm


After researching a bit on what found with Google search (if not where?) I decided to give a try to the OnTime method. And I should admit that my previous prejudices weren't so well based, so I build this test workbook. It works fine, at least in this stage.


How to use it:

a) Fill the task data (Id, Name, Enabled, Scheduled start, Duration, Delta time, and Finished

b) Use green button "Start tracking" for triggering the procedures to be scheduled (for Scheduled start, About to end, Just in time, and Delayed columns)

c) Use red button "Stop tracking" to remove pending triggered procedures

d) Use red button "Reset tracking" to make a factory reset, removing all actions of previous trackings

e) Use green and red buttons for filling column C (Enabled) only if you're as lazy as the author

f) Cell A1 backcolor reflects tracking status: green On, red Off, no color Reset.


After having started tracking if you fill in column J (Finished) all pending events of that task would still execute, but with no effect as the related witness cell (of each 4 of the involved columns) will display "<N/A>".


And if you input a "Y" in a non tracked task then all 4 procedures will be checked for add to actual schedule.


Colors (just for using the standard ones, otherwise you may replace the value constants for those weird values as &H00F0Cxxxx, I rather chose vbRed, etc.):

-----

[pre]
Code: 
Const gklColorIndexNone = xlColorIndexNone
Const gklColorMissed = vbMagenta
Const gklColorScheduledStart = vbCyan
Const gklColorAboutToEnd = vbGreen
Const gklColorJustInTime = vbYellow
Const gklColorDelayed = vbRed
[/pre]
-----


I don't know if this should be useful, but with a lot of tasks you'll have to be very careful to program and build schedules... unless you want to get killed by your users.


Just advise if any issue.


Regards!


PS: If it's not able to be installed in a user friendly implementation, you'd still may save a few bucks at Christmas times replacing the tree and its lights for this workbook at full screen. Or better on a 100" external TV.
 
sir. thanks. i will review today and tomorrow. will provide you feedback no later than monday.


very much appreciated!

very much appreciated indeed!
 
Hi SIR!

I tested the sheet but it did not perform as expected

here is what i did ( please let me know your email address to send you the sheet as i used and formatted it. i think it would be ideal if you give my file a look since i made some modifications on the sheet --format wise-- )


ok

i made sure before testing the code to erase all previous data and to save the file with tracking feature Stopped

The only data i hardcoded and saved in the file were cells e2 and e3 edited with a 00:10:00 which represents a targeted duration of 10 minutes;and cells f2 and f3 edited with 00:05:00 which represents a threshold of 5 minutes


i specifically made the following actions:


a) after doing the above i opened the file and typed a task id equal to 1; then i typed in a name followed by time stamping cell d2 via the abbreviated shortcut of CTRL + SHIFT + period with the current time. then i typed in a Y on cell c2 and finally clicked the start tracking button


i was expecting cell g2 to change to green background once the scheduled+duration-threshold calculation equalled the current time but it did not.

btw...i am assuming the code is doing the tracking of current time and comparison to the calculation above, internally without a way to notice it on the actual spreadsheet


ok; the remaining cells h2 and i2 behaved just like that

no visual cues popped as time elapsed (nor green nor yellow nor red)

the time control cells just changed to magenta as soon as i typed the initial data described above. curiously those 3 cells showed a value that was the scheduled data time plus the duration time. but the cells experienced no change whatsoever after that


my understanding on how to use the file is that everytime i enter a new id number (consecutive number) i should type in the id# then a name then time stamp the scheduled start cell then enable the cell with a Y value, and always click the start tracking button. i assume i must always click that button as i add further records

am i correct in my understanding?


also i am assuming i should make sure that before entering a new record or id num, the previous record has a time stamp on cell J (or just to make sure that cell value is not left blank if i will enter further new record)


essentially

i plan to use the sheet to control the assistance/presence of people in a recreation room. the threshold will always be 5 minutes and the expected duration of 30 minutes.


all id numbers (or consecutive records) will have no relation between them.


the name of the person might repeat itself but essentially i wanna be able to track the duration of their stay in the room on 30 minutes intervals. Giving the person a 5 minute threshold free of charge (when the scheduled time plus duration time is equal to the actual time clock in excel; that is why the visual cue of cells in column I is the most important cue of all)


my deepest apologies if my previous request was not cleared enough

i sincerely thank you for your help before and i hope you have the patience to give it one last crack at the file


i await your response during the next days

thank you SO SO much!
 
Hi, becha!

Long answer, I'd be analyzing it in a few days and I'll come back here.

Regards!
 
Hi Sir!!

Would you please check on mi request sometime this week?

i am approaching deadline for file delivery. my boss is giving me the looks!

:)

no seriously...i would really appreciate if you can assist me with the request

i have the modified file at hand. let me know if you need me to uploaded someplace

i await your feedback

humbly,

becha
 
SirJB7!!

Hello!!!


Would you be able to check on my request this next 2 days or next week?


i am approaching deadline for file delivery. my boss is giving me the looks!

:)

no seriously...i would really appreciate if you can assist me with the request


i have the modified file at hand. let me know if you need me to upload it someplace


i await your feedback


humbly,

becha
 
Hi, becha!

I apologize, I actually had this topic bookmarked but somehow it got deleted. Tomorrow I'll check it and come back here.

Regards!

PS: First let me check what you did before and if something needs to be changed and then we'll see if you should upload your file.
 
hello SirB7

have a great start of the week


my apologies in advance for going back to you with this request:


do you believe you can help me this week?


i really need the file as my peers and boss are constantly checking up with me


so sorry for my insistence. i anxiously await your reply


hopefully by tomorrow i can start working with the spreadsheet and have all my colleagues impressed!


of course, all will be thanks to you Sir!


kind regards
 
Hi, becha!


I tested your procedure with shortened times for testing purposes and the workbook behaved as expected, except that Ctrl-Shift-Period sets only the actual time but not the actual date, so that shortcut isn't valid for this case since it's required a full date and time value.


I made a little houseclean to the procedures and also added a cyan button to check the tasks scheduled that are pending at any moment. But I didn't change anything of its behavior.


Please download again the updated file from same previous link, and test it again entering values normally in column D, and if it doesn't work as expected then upload a sample file.


Regards!
 
hi Sir

the sheet doesnt seem to work

even though i am using excel in english do you think the problem could be how the date and time are set up in my laptop?

i live in mexico

here is what i did. i opened the downloaded file. i clicked stop tracking. i unprotected sheet. i deleted all the records. just left the 1st one. modified the starting hour (made sure to edit a date as you specified on prior comment). modified the duration and the threshold to read 5minutes. then i flaged the cell with a Y for enabled and finally pushed the start tracking button.

there were no signs of functionality on the file after those steps. i even clicked the track view and window stated true - 0 tasks

can you lend me a hand and share your knowledge on this?

i wonder what the heck might be the problem

thanks a lot

ill be awaiting

take care
 
Hi, Becha!


My uploaded file works, I've tested it with the last 2 rows, 7th & 8th, for Task ID 6 & 7, check columns D and G to see the date, a few minutes before uploading, and last row shows 1st event missed (scheduled start), 2nd event triggered (about to end), and 3rd & 4th events interrupted (disabled).


Would you try it again doing this? 1) stop tracking, 2) reset tracking, 3) unprotect workshet, 4) delete all but one tasks, 5) set new starting time, 6) adjust duration & delta time, 7) enable task.


If that doesn't work the only thing I could do is check the file you might upload but I wouldn't bet a buck on it.


Regards!


PS: BTW I'm in Argentine and run machines with Win7x64 & Office2010x86 both in Spanish, and I don't think that regional setting configurations interfered as yesterday and today dates aren't valid dates if switched day & month (17/06 vs. 06/17), if you see the date properly displayed in "ddd dd/mm/yyyy hh:mm:ss" format the you should discard this issue.
 
ok i will download file again and follow your instructions.

excuse me but in the steps you described when i should click the start tracking button?? as step 8 or before enable task?

gracias!
 
Hi, becha!

The StartTracking button does the same thing for each task as its enabling/disabling. So as in the test example there was only one task, the last step, 7th, was placing a "Y" in column D there was no need to press that button.

Regards!
 
You must log in or register to reply here.
Back
Top