Solved

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

Posted on 2016-10-18
Medium Priority
63 Views
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.

28977190.xlsm
0
Question by:Omar Hernandez
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 10
• 9

LVL 49

Expert Comment

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

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 49

Expert Comment

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

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

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

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 49

Expert Comment

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

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 49

Expert Comment

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

Author Comment

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 49

Expert Comment

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

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 49

Expert Comment

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

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 49

Expert Comment

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.

---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

ID: 41852525

Copy and paste on the search bar of youtube

K Column and userform

Hope this works
0

LVL 49

Accepted Solution

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

Author Closing Comment

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 49

Expert Comment

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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Use Windows Task Scheduler to print a Word document weekly so your printer ink won't dry out.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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 â€¦
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaacâ€¦
###### Suggested Courses
Course of the Month10 days, 23 hours left to enroll