Search 74,114 tutors
FIND TUTORS
Ask a question
0 0

how to make a time to completion chart

Tutors, please sign in to answer this question.

1 Answer

Hi the steps take a couple of minutes but this should help you. 

In A1 enter the date and time you want to finish your project 
In A2: =now()
In A3: ="Months " & DATEDIF(A2,A1,"m")
In A4: =" Days " & DATEDIF(A2,A1,"md")
In A5: =" Hours " &TEXT(A1-A2,"hh")
In A6: =" Minutes "&TEXT(A1-A2,"mm")
In A7: =" Seconds "&TEXT(A1-A2,"ss")

In A8: =A3&A4&A5&A6&A7 After you have these steps done you will want to make it so that the time countsdown in real time so you that you do not have to close and open it over and over again. to do that press the ALT F8 key combination, enter aaa in the macro name, then press edit. This will open the VB editor and give the sub outline headings you will see Sub aaa() and End Sub what you type in you want to look like this

Sub aaa()
Application.Calculate
Application.OnTime Now() + TimeValue("00:00:01"), "aaa"
End Sub

Once you have that input close it and go back to the excel sheet. Press alt F8 again and this time select the macro you created and click run. The timer will then start counting down from current time and will continue. If you want to make it into a chart simply select the cells that you put information in and go to insert , then selet the chart you want. A word of note however is that when you turn this information into a chart it will not have anything that moves in it. If you are trying to juct make a regular chart input your data in the cells then use the same steps as you would once you finished your countdown timer, select your cells with information then go to insert and select the chart you want. I hope this is useful. 

Comments

You are good with Excel, my man! Spent hours on this one night, never could figure it out on my own. Thank you! Can we take it one step further and include the time on that date? I can't get the datedif() to calculate anything besides just the date.  How can I calculate between now and 5:00pm on a certain date ie the due date and time? I also ran into a problem where only the seconds increment or decrement.  I don't know how to fix this... pls help? thanks.

Ian F.

I found that if you do the hours minutes and seconds in 1 cell it increments better.

I have it this way: in a5 =TEXT(A1-A2,"hh:mm:ss")

For some reason it wont increment properly, for me, if you have each hr, min, sec increment in their own cell. However =TEXT(A1-A2,"hh:mm:ss") seems to work like a charm.