Use real time clock within a spreadsheet?

[mattarmy]

Hi!

I'm new to Excel and I want to do something that seems to be pretty
complicated (for me)!

I want to insert a clock onto a sheet and have it update in real time
when I start the program. It also then needs to count down for a given
period of time.

Also, I want it to update two other variables which would be defined
dependant upon how much time has elapsed. For example it goes from 10
to 20 after 5 minutes, 20 to 50 after 10, and so on.

Is there any easy way for me to do this? I'm a bit of a newbie to the
more complicated features of Excel but I understand Macros a bit so
some steps in the right direction would be a great help.

Thanks for all your advice in advance.

MATT

 

[Bob Phillips]

Matt,

You can find a real time clock at
http://xldynamic.com/source/xld.XtraTime.html. Assuming that I get the time,
I will update it to have a countdown tomorrow (it is late here now), so keep
checking back. Can you explain that bit about two variables a bit more?

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)

 

[mattarmy]

Thanks Bob,

The two variables will simply be set values from a different sheet.
Every 15 minutes it would move from A to B to C and so on in that
sheet.

Hope that makes sense, thanks for all your help so far.

MATT

 

[Tushar Mehta]

A custom solution from Bob will probably be the least work for you. But,
here's something that could keep you going in the meantime.

For the countdown timer (and for one possible approach to a real time
clock):
Clocks
http://www.tushar-mehta.com/excel/software/clocks/index.html

To implement your own real time clock in an Excel cell, you can do the
following:
Add the following code to a standard code module:
Option Explicit

Dim NextTime As Date
Sub RepeatOneSec()
Application.Calculate
NextTime = Now() + TimeSerial(0, 0, 1)
Application.OnTime NextTime, "RepeatOneSec"
End Sub
Sub EndProcess()
Application.OnTime NextTime, "RepeatOneSec", , False
End Sub

[For more on the rationale behind the above code see
How can I make a cell (or some other object) flash?
http://www.tmehta.com/tmxl/dbfind_byid.asp?id=14]

Now, in any cell, enter =NOW() and format it to show the time as desired.
Initiate the above code by running the RepeatOneSec procedure. This will
recalculate your application every second (yes, that can have consequences)
and in the process the cell with the NOW formula will update.

To stop the clock, run the EndProcess procedure.

To implement the step functions you want, in some cell, say C15, enter the
starting time reference (including the date). For more on how, see the XL
help topic 'Insert the current date and time in a cell'

Now, in some cell, say E15, enter the formula =MINUTE(NOW()-C15). This will
give you the elapsed time in minutes since the base date+time in C15. You
can use this as a trigger for whatever you want. For example, in some cell,
enter the formula =IF(E15>10,50,IF(E15>5,20,10))

Hi!

I'm new to Excel and I want to do something that seems to be pretty
complicated (for me)!

I want to insert a clock onto a sheet and have it update in real time
when I start the program. It also then needs to count down for a given
period of time.

Also, I want it to update two other variables which would be defined
dependant upon how much time has elapsed. For example it goes from 10
to 20 after 5 minutes, 20 to 50 after 10, and so on.

Is there any easy way for me to do this? I'm a bit of a newbie to the
more complicated features of Excel but I understand Macros a bit so
some steps in the right direction would be a great help.

Thanks for all your advice in advance.

MATT

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions