Contact Me!!!

spiders

down to end and the site search box

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

Enough? [contact me if this helped you ]

back to the top


Go on...vote for Fatblokeracing.org .... ORIGINAL CONTENT © John "Fatbloke" Bullas | To Link to FBR click HERE | HTMLBLUNDERS? Click Here!!!! please