Task List Countdown

[bstretton]

Hi -

Hoping someone can help me with a spreadsheet that my husband wants
He is addicted to a game called Elder Scrolls Online.
He has a list of about 10 tasks in the game that have a cool down period.
He wants a spreadsheet that will help him keep up with these tasks.

For example:
Task 1 = 20 Hours
Task 2 = 10 Hours
Task 3 = 10 Hours
Task 4 = 15 Hours

When he opens the spreadsheet, he wants a cool down timer for each task that will tell him how long is left until he needs to complete that task again. So it needs to be a timer that when it is started, it keeps counting even after the spreadsheet is closed. When he completes the task, it needs to have a "restart" button that will allow him to restart the cool down period again.

I hope that makes sense. Please let me know if you can talk me through this or if more information is needed. Or if there just isn't a way to accomplish this in excel.

It seems a bit ridiculous but I told him I would try to do it for him b/c he just bought me an iMac for my birthday... so I guess I should do something nice for him!

Thanks,
Brandy

 

[RickXL]

Hi,

I hope this all works on a Mac. I can't test it because I only have a PC.

It looks like this:

You will need to keep a clear head because there are lots of details.

► Basically, the start time is recorded in column B.
► When you hit a reset button that changes the time in column B to the current time using the VBA Now function.
► That means that the date and time in column B will be in the internal Excel date/time format.
► Column C also needs to be in the same date/time format but the date is not displayed. The format should be set to [h]:mm (the [h] allows times over 24 hours to be displayed without needing a days digit).
► There is a macro which recalculates the spreadsheet every minute.
► It also tells itself to run after a pause of a minute.
► That has to be started off by the Workbooks Open event automatically when the workbook opens.

So, this code needs to go into the Thisworkbook macro section:

Private Sub Workbook_Open()
    sheet_calc
End Sub
[code]

That starts the timing cycle when the workbook is opened.

This is the code associated with the buttons. The code needs to be in a Sheet module - the same sheet as the buttons are on:
[code]
Private Sub CommandButton1_Click()
    Range("B2").Value = Now
End Sub
Private Sub CommandButton2_Click()
    Range("B3").Value = Now
End Sub
Private Sub CommandButton3_Click()
    Range("B4").Value = Now
End Sub
Private Sub CommandButton4_Click()
    Range("B5").Value = Now
End Sub

Finally, this needs to go into an ordinary new macro Module:

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

The timer is not really working when the workbook is closed. However, real times have been used everywhere so the computer clock will be used to re-calculate the remaining times at the first re-calculate when the workbook is re-opened.

I hope this makes sense.

Best of luck!

 

[bstretton]

Thanks! I will give this a go in a bit and see if I can make it work. I'm a bit nervous but I'll try

 

[RickXL]

It might help if I got my ending tags sorted out. Part of my previous post should have looked like this:

So, this code needs to go into the Thisworkbook macro section:

Private Sub Workbook_Open()
     sheet_calc
End Sub

Anyway, if you get stuck you can always come back.

 

[bstretton]

Ok. Which part actually makes column D actually count down? I don't have anything working in that column yet. I'm trying to make sure I have all the code in the appropriate places in VBA but I'm not sure.

These are the pages I have in VBA right now.
(and I have switched back to PC to make this. not on my Mac making it - b/c he runs a PC from his side of the couch!)

 

[RickXL]

Hi, I am not quite sure why you have two Modules.

There should be one with this in it:

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

If you double click the one called Thisworkbook there should be:

Private Sub Workbook_Open()
     sheet_calc
End Sub

and behind Sheet1 there should be the buttons. You will need to add the buttons manually to the worksheet. I used the ActiveX ones. When you double click them they should permit the button code to be added. The first and last lines should be automatic so you will need to just add the middle one.

 

[bstretton]

Just out of curiosity, what are of the Midlands are you from? My husband is from the West Midlands - outside of Wolverhampton.

 

[RickXL]

Hi, Derby/Staffs border.

I just realised that I did not answer your previous question - the counting down part.

A good question because I have left out a vital part of the process. There is a formula that needs to go into column D to calculate the time left.

=B2+C2-NOW()

The macros just cause that formula to recalculate every minute.
Obviously, without that formula nothing much was going to happen.
I apologise for that.

Put that formula in D2 then fill down to the rest of column D.

Regards,

 

[bstretton]

You're really awesome!! I think I got it working like your example... which is awesome.
Now... another question that my husband has just asked. Do you know of any way possible to have the countdown clock turn RED when it hits the last 60 minutes of the countdown?

 

[RickXL]

Hi,

Thanks for the compliment

As for turning red, you can use Conditional Formatting. This can be tricky to set up as well.

First, highlight cells D2 to D5.
On the Home tab, find the Styles section and select Conditional Formatting.
Select New Rule from the dropdown.
Select "Use a formula to determine which cells to format".
In the box under "Format values where this formula is true" enter:

=D2&LT;TIMEVALUE("1:00:00")<timevalue("1:00:00")<timevalue("1:00:00")<timevalue("1:00:00") <timevalue("1:00:00")


That will return TRUE when D2 is less than 1 hour.
Hit the Format button.
Select the Fill tab.
Click your preferred shade of red.
Then OK all the way out.

If you selected D2:D5 then that rule should apply to all cells. The D2 you entered will change to D3 on the next row, for instance, automatically.

Test them by entering a duration of 1:01 i.e. 1 hour 1 minute then resetting the start time. It should go red in a minute.</timevalue("1:00:00")<timevalue("1:00:00")<timevalue("1:00:00")>