Auto Automatic Regular Refresh Excel Spreadsheet to create make a clock
or elapsed remaining time counter
Ok, assuming you have a series of cells containing formulae to extract
the hours minutes seconds or days for an incrementing or decrementing
value using round() and/or Int() and/r MOD()
for example:
=DATEVALUE(C6&"/"&C7&"/"&C8)+(C9/24)
will create a serial number with
C6 as the day (1-28/30/31st etc)
C7 as the month (1-12)
C8 as the year (1901 onwards)
C9 is the hour (0-23)
c11=NOW() is just that
=+C5-C11 will give you the difference, how long from now to then
C2=INT(C5-C11) will give you the DAYS until then
D2=INT(24*((C5-C11)-C2)) gives you the hours remainder
E2=INT(MOD((C4-INT(C4))*(24*60),60)) gives you the minutes remainder
OK, how to get this baby to autorefresh to give a semi-moving display?
Create a text file with a single digit in it ... save it as DUMMY.TXT
in the same directory as your 'clock'
Next select a cell and go DATA >IMPORT EXTERNAL DATA >IMPORT DATA
SELECT DATA SOURCE >OPEN>
Point the target to a blank cell in a new worksheet
External data range properties >>> turn 'prompt for filename'
off and set REFRESH EVERY..... to the interval
NOW next time you open this workbook you will (i) be prompted to enable
macros and (ii) to enable auto refresh
What you now will have is an autorefreshing worksheet WITHOUT the use
of VB or MACROs that you had to write!
A font for your 'clock'? try Transponder AOE, but it only supports NUMBERS
and you might choose Terminal for the rest?
JB
Page updated: Tuesday, December 27, 2011 2:44 AM
|