Refresh current Date & Time in Excel 2007

Hello,

I have got the =NOW() function entered in a cell to display the current Date and Time.  Right now it only refreshes when I enter or change the data in a cell somewhere in the worksheet.  Can you tell me how to refresh it every 1 min or 2 min automatically, regardless of entries being made?

I've tried going to the Data tab and clicking the dropdown arrow under the Refresh button but the Connection Properties option is grayed out so I can't go in to set automatic refreshing.

Any suggestions would be much appreciated.

Thanks
Steve_BradyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajvjaCommented:
0
krishnakrkcCommented:
Hi,

try

Kris
Dim NextRefresh As Date
Sub StartRefresh()
RefreshNow
End Sub
Private Sub RefreshNow()
NextRefresh = Format(Now + TimeValue("00:00:05"), "dd-mm-yyyy hh:mm:ss")
With Range("a1")
    .NumberFormat = "dd-mm-yyyy hh:mm:ss"
    .Value = NextRefresh
End With
Application.OnTime NextRefresh, "StartRefresh"
End Sub
Sub StopRefresh()
On Error Resume Next
Application.OnTime NextRefresh, "StartRefresh", , 0
End Sub

Open in new window

0
krishnakrkcCommented:

replace

TimeValue("00:00:05")

with

TimeValue("00:02:00")

for two minute gap

Kris
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Steve_BradyAuthor Commented:
Thanks,

Can you tell me exactly what to do with above code?  i.e. copy/paste?  paste where?, do I open something or paste in a cell?  etc.    Sorry, I'm not familiar with programming.

Thanks
0
krishnakrkcCommented:
Hi,

Open Excel > Hit Ctrl + F11 > Insert > Module and paste the code there.
Hit Alt + Q. Now Go to tools Macro > Macros and run StartRefresh.

This would update the date and time in A1 on activesheet.

See attached file.

Kris
Refresh-Date-Time-Automatically.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Steve_BradyAuthor Commented:
Thnx Kris...that's just what I needed.  I appreciate the detail.  One thing:  can u tell me what Ctrl+
F11 does (i.e. is there another way to do it?) cuz I've got F11 set to do another function.
0
Steve_BradyAuthor Commented:
For your "Insert" step after Ctrl+F11, is that the Insert tab or something else?  I'm in Excel 2007.
0
krishnakrkcCommented:
Hi,

Insert Module.

For a tutorial on VBA see the link below.

http://www.functionx.com/vbaexcel/Lesson02.htm

Kris
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.