EXCEL current Time

[oldtimer]

I have a problem, or rather my daughter has a problem which I cannot solve and my superdad status is in jeopardy.

How do you put the current time into an excel spreadsheet in such a way that it constantly updates whilst the spreadsheet is open?

I have tried variations of NOW() without success.  Perhaps it is impossible, but I guess if anyone knows they will be on this site.

From what I can make out she is trying to construct some sort of timetable.  She said “think of a bus time table that continually tells you how long to the next bus”.  Unfortunately she lives in a small town  in north Canada so I cannot just pop round to see her.

There is one small problem – I am using EXCEL97.  I do not know what version she is using but it is probably more up to date than me.

Its easy when you know how

[Dave Rice]

AFAIK there is no way to do this without resorting to VBA.

Even then I’ve only seen it used as some sort of timer with stop and start buttons.

[Richard]

I think Dave is right, this is a link to what I found with a web search:

https://sites.google.com/site/excelforbeginners/Home/vba-codes/clock-with-automatic-refresh

I cannot vouch that it will do the exact task you want as I have not tested it, but maybe there are other examples on the web.

[StevieP]

Wouls something like this be any good http://bit.ly/2mIvFqM

[Robin Long]

Obviously, you can easily insert the time with “Ctrl+Shift+;”

or you can enter the command “=now()” which gives you the date and time, clicking on formula – “Calculate Now” in the ribbon will update the value of any cell using the =now() formula, that’s the easiest way. Else it’s Macro’s/VBA.

 

Cheers Knight,

RIP Spike09 Your Missed
If I'm not here, I'm there.

Finally joined Twitter! longr79

[oldtimer]

Thanks all.  My faith has not been misplaced.  I was afraid that some sort of macro would be required but my brain is too slow nowadays.   I have already tried the “excelforbeginners” site plus a few others that promised the world but failed to deliver.   I will email the links over to her.  She is much more with it than me.  I am not totally sure what she is aiming for because I cannot see a use for a continually self-updating spreadsheet unless she wants to re-programme all the arrival and departure boards at the local airport?.  For anything else I would go with Robin and just update when the occasion arises.  But there we go, Dad has some funny ideas at times?

Its easy when you know how

[Robin Long]

Further to my earlier post:

1 On the Ribbon open up ‘Developer’ (you may have to add this with the customize ribbon command(right click on any title in the ribbon and click ‘customize the ribbon’) Select Macro Security and Enable(this step is entirely optional but if you want completely automatic you’ll need too).

2. In the desired cell enter =NOW()

3 Click on Macros

4 in Macro name enter a name for your macro ie. Auto_Open and click create.

5 delete everything in the macro box and replace with

Sub Auto_Open() (<- name of your macro)

(indent) calculate

End Sub

6 Save the macro exit macro editor, save the workbook as a macro-enabled workbook file then exit the workbook.

Reopen and the sheet should auto update the time

attached file contains macros duh! and was created using office 2016

 

Cheers Knight,

RIP Spike09 Your Missed
If I'm not here, I'm there.

Finally joined Twitter! longr79

[Author]

Posts