Solved

Have the left number set as a countdown on days that are passing by.

Posted on 2016-10-18
19
39 Views
Last Modified: 2016-10-20
Hello there Experts Exchange users and helpers. Would appreciate any help for the following functions.

Have the left number set as a countdown on days that are passing by.

For example the day that it was created was on oct 15 and the present day now is Oct 19, four day has pass so now it will look as follow 9  !  13 instead of it looking like  13  !  13

A. Countdown on days, starting on the number input on (GIVEN #)
B. When i click on any row that has already a Countdown and Given # have it show when i click on the Number userform.

C. When countdown reaches zero have it display with the word TODAY, but with given # always showing what was the original number input there.

D. If days is shown TODAY on column "K" and i have already taken care of it if i press the actual row that says TODAY/userform if i click on the button that says "RESET" it will start the countdown in number 13.

Have-the-left-number-set-as-a-countd.png28977190.xlsm
0
Comment
Question by:Omar Hernandez
  • 10
  • 9
19 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41850424
For example the day that it was created was on oct 15...
How do I know when it was created?
0
 

Author Comment

by:Omar Hernandez
ID: 41850472
is it possible to have it in a way that it creates a formula on the column "K" column if it is easier ?
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41850474
Also how does the 9 get put in the "Count Down" textbox; manually, or automatically when the userform is opened? If the user can enter anything he wants, why do I need to know when the task was created?

I guess I really don't understand the purpose of the countdown.
0
 

Author Comment

by:Omar Hernandez
ID: 41850483
It is there just in case if for some reason i may not attend or finish the task on the given day let's say it is schedule for in three days. In count down it will show "3" and i know i am going to be busy i would go to countdown and change it to "4" or other number.
0
 

Author Comment

by:Omar Hernandez
ID: 41850491
For example the day that it was created was on oct 15...
How do I know when it was created?

Would it be better to have the "K" column cell to have a formula in that cell while also counting down the days.

or

Have it in a way where i can have an additional Column where Column "K" will retrieve it's information.
0
 

Author Comment

by:Omar Hernandez
ID: 41850508
The purpose of this function is so that i may review a material from school and i won't forget, let's say i have finished understanding a task, but want to make sure that i don't forget in a week or two from now. What i would do is set up a given number so that i may review it.

---If i put a number on given #, let's say it being 8
---it will count down 8 days from then
---once it gets to zero, it will be display to "TODAY.

Hope this information works and give you a better understanding what i want this file to accomplish.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41850517
is it possible to have it in a way that it creates a formula on the column "K" column if it is easier ?
It would not be hard either way (VBA or formula) but I can't do either one until you answer my "How do I know when it was created?" question. Is the create date the same as column "A"?

If I create a new task and column K shows "5 ! 5" and I close the workbook and come back to it 3 days later, should column K say "2 ! 5"? If so what should happen if I manually change it to "3 ! 5", close the workbook and open it the next day?
0
 

Author Comment

by:Omar Hernandez
ID: 41850608
It would not be hard either way (VBA or formula) but I can't do either one until you answer my "How do I know when it was created?" question. Is the create date the same as column "A"? This would be ether having the date on the column "K" imprinted beside the code, if it is possible doing it this way or by having an additional column "L" have it countdown from there. So if in the userform i have in the Given # as 8 it will input in column "L" Oct 28 and count down from there.



If I create a new task and column K shows "5 ! 5" and I close the workbook and come back to it 3 days later, should column K say "2 ! 5"? If so what should happen if I manually change it to "3 ! 5", close the workbook and open it the next day? it will push the day, due to it being changed. for example as you said you change it from "2  !  5" to  "3  !  5" it will record new update and will countdown from "3  !  5"
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41850672
I still don't understand.

1) Let's assume that we are using column "L" as the create date and that today we create a new task 5-day task called "Read the Iliad" so column "K" would be "5 ! 5" and column "L" would be "10/19/2016".

2) If I reopen the workbook on 10/21/2016 I assume that all values in column "K" should be recalculated based on the current date  and so  the "Read the Iliad" task should say "3 ! 5". Is that correct?

3) Let's also assume that on the same day (10/21/2016) I change column "K" for the "Read the Iliad" task so that it says "4 ! 5".

4) If I reopen the workbook on 10/23/2016 should column "K" for the "Read the Iliad" task say  "1 ! 5" (because it's been 4 days since it was created and 5 - 4 = 1), or should it say "2 ! 5" because you added a day in step 3? If you say that it should be "2 ! 1" how do I calculate the "2" since 5 (the task length) minus the difference between current day and the create create day is "1" (5 - (23 - 19) and not 2?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Omar Hernandez
ID: 41850730
1) Let's assume that we are using column "L" as the create date and that today we create a new task 5-day task called "Read the Iliad" so column "K" would be "5 ! 5" and column "L" would be "10/19/2016".

2) If I reopen the workbook on 10/21/2016 I assume that all values in column "K" should be recalculated based on the current date  and so  the "Read the Iliad" task should say "3 ! 5". Is that correct?
Yes this is correct.

3) Let's also assume that on the same day (10/21/2016) I change column "K" for the "Read the Iliad" task so that it says "4 ! 5".
---I would like it to have it in the following way that if i would like to change the day/number i would need to go to the userform if in the date of 10/19/2016 i have put a Given #  5
--- If i would like to change the date and add an extra date on 10/21/2016 i would need to open again the userform on the row that has the task "Read the iliad"
---I will always see on the Given # the number i have gave to it this being 5, but on the countdown side  i will see 3 on 10/21/2016.
---To add an additional day i would go to the Countdown side and replace 3 with 4 and on the actual row of "Read the Iliad" on column "K" i would now see see it updated to " 4  !  5"

4) If I reopen the workbook on 10/23/2016 should column "K" for the "Read the Iliad" task say  "1 ! 5" (because it's been 4 days since it was created and 5 - 4 = 1), or should it say "2 ! 5" because you added a day in step 3? If you say that it should be "2 ! 1" how do I calculate the "2" since 5 (the task length) minus the difference between current day and the create create day is "1" (5 - (23 - 19) and not 2?
---On 10/23/2016, due to the update we have made on 10/21/2016 in reference of your 3rd question will be now be "2  !  5" on the "Read the Iliad"
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41850798
When the workbook is opened I still don't see how Excel will determine that it should be "2 ! 5".

Instead of the approach that we've been talking about, how about we just use columns "A" and "B". If we did that then a task entered today (10/19) with column "A" having 10/22/2016 and column "B" having "10/27/2016" would show "5 ! 5" in column "K", and would continue to show "5 ! 5" until 10/23 when it would show "4 ! 5", and on 10/24 it would show "3 ! 5". In this scheme if you wanted to add a day to the task you would just change column "B" to "10/28/2016" using the calendar form and you would have no need for the "Count Down", Given #" or "ENABLED" controls on the NumberArrange form.
0
 

Author Comment

by:Omar Hernandez
ID: 41850976
Column A and B it will be used for the purpose of column "J"

When the workbook is opened I still don't see how Excel will determine that it should be "2 ! 5".?
---On the userform if i put Given # to be 5 have this date generated to column "L" as 10/24/2016
---Countdown will be counting down 5 days from now, when it gets to zero it will show TODAY. on column "K" it will show TODAY when date is present to 10/24/2016.
---If i want to update and add an additional day i would change the number that is also counting down from the userform. if three days have pass the userform will have Countdown being 2 and given # 5.
---on the userform if there is a number on the Countdown and i would like to change it to be on a later date, what i would need to do for example,  if present day is 10/22/2016 countdown is 2 and given # is 5.
---If there is a number counting down on the userform. It will ignore the Given # information
----If i change only the Countdown number 2 to 3 on the userform, it will update the column "L" from date 10/24/2016 to 10/25/2016 because i have added an additional number.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41851230
Try this. I would not be surprised if there were problems. I also added an indicator that tells you what color is selected on the userform.
28977313.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41851306
The Update as to indicate what color is being selected is very cool. Very useful.

---The coding is pretty much heading on the right direction because when ever i close the file it does do changes, but i am mainly looking for it to do the following.

---Instead of closing it and have information update, have new information update when the Refresh button on file  is clicked. Remember that this refresh button has other function as well, so have it in conjunction with the new functions.

---On Given # if i input a 5 on it and the Countdown is blank if i click generate have the number 5 on the given # also pasted automatically on Countdown.

---Today being 10/19/2016 if i add on given number 5 it will automatically display on column "L" 10/24/2016.

---On the userform because now there is a number on the Countdown, it will start counting down the days all up till it get to 10/24/2016.

---Let's say that present day is now 10/22/2016 on column K i should see "2  !  5" and still see on column "L" same date as originally input as 10/24/2016.

---When making new updates to push up date, Let's say that present day is still 10/22/2016 if i go to the userform on that specific task that has date 10/24/2016 on column "L" i will go to the userform and see that Countdown is 2 and given is # 5. If i change 2 to 3 and click close button on the userform it will input automatically a new date to column "L" as 10/25/2016.

---Remember that any time that there is a number in Countdown it will take the number of the countdown and ignore that number that is on Given #
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41852333
---Instead of closing it and have information update, have new information update when the Refresh button on file  is clicked. Remember that this refresh button has other function as well, so have it in conjunction with the new functions.
Done

---On Given # if i input a 5 on it and the Countdown is blank if i click generate have the number 5 on the given # also pasted automatically on Countdown.
Done

---Today being 10/19/2016 if i add on given number 5 it will automatically display on column "L" 10/24/2016.
It already does that

---On the userform because now there is a number on the Countdown, it will start counting down the days all up till it get to 10/24/2016.

---Let's say that present day is now 10/22/2016 on column K i should see "2  !  5" and still see on column "L" same date as originally input as 10/24/2016.

---When making new updates to push up date, Let's say that present day is still 10/22/2016 if i go to the userform on that specific task that has date 10/24/2016 on column "L" i will go to the userform and see that Countdown is 2 and given is # 5. If i change 2 to 3 and click close button on the userform it will input automatically a new date to column "L" as 10/25/2016.
I'm not sure if you were asking me to change anything.
28977313a.xlsm
0
 

Author Comment

by:Omar Hernandez
ID: 41852525
Hello Marting Liss please check out the video posted on youtube for a more broader detail.

Copy and paste on the search bar of youtube

K Column and userform

Or Click here  https://www.youtube.com/watch?v=2_ArTgRToqI

Hope this works
0
 
LVL 45

Accepted Solution

by:
Martin Liss earned 500 total points
ID: 41852992
Here's an update.
28977313b.xlsm
0
 

Author Closing Comment

by:Omar Hernandez
ID: 41853004
This actual file you have provided has accomplished what i wanted, i really appreciate your help and time Martin Liss. I will be posting another question soon, but will close this question due to you having my question answer. Thanks again.
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 41853005
You're welcome and it's up to you of course, but as the next question I'd really like to fix the end date before start date problem.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now