Timers

QuestionEdit

I am attempting to create a spreadsheet which will count down the number of years,days,hours,minutes,& seconds from a now() formatted cell & another random date. some will be future dates, some of the dates will have already passed. I am creating a worksheet of termination dates for various products. I would like to have a cell adjacent to the termination date cell, which is a living counter, meaning everytime you open the worksheet, it has a live clocking of time remaining from now to the expiration date. I have found a couple of solutions on the web, & have a sheet with a clsTimer module inserted, so I have been able to create a worksheet that counts, however, I cannot seem to get the formatting just so.

for instance, I would like the cell to read as yy:#of days:hours:minutes:seconds, & I cannot get the days to calculate beyond the 30/31 days of the months, etc... I would like to have some cells read as 14 months, instead of 1 year 2 months, etc.. 
I was also hoping to have the cells highlight depending on the time left, meaning when a termination date approaches 30 days, I would like i tto turn red.

Sorry if this is confusing, I am a total novice on excel, & trying to create a very functional worksheet, & certainly struggling a bit. I would greatly appreciate your time & any help you would offer. Sometraining is certainly in my future!!!!

BW

AnswerEdit

I have a few questions about your problem:

1. You refer to the format yy:#of days:hours:minutes:seconds. I assume yy refers to years. So I would think that if the date is 2 years, 62 days, 13 hours, 45 minutes and 2 seconds you would want the result to look like "02:62:13:45:02".

But later you refer to wanting the cells to display months. This would be a bit problematic to interpret because different months have different lengths in days. Of course, years has this problem also because of leap years. Representing the time in dddd:hh:mm:ss would not have either problem. Would this be acceptable?

If not, please provide an example of a specific number of years, months, days, etc., showing how you would like to see it displayed.

2. You mention turning the the cell highlighting red when a termination date "approaches" 30 days. Does this mean it will turn red at 32 days? Should it stay red when less than 30 days? Or do you simply mean that it should turn red when the count down is less than or equal to 30 days?

3. I think that explaining how to do this would be more difficult than showing how to do it. If you would like to email me your worksheet I would add the code to it and return it to you with some explanation of how it works. My email address is VBAexpert-AT-myway.com (replace -AT- with @).