?
Solved

Refresh current Date & Time in Excel 2007

Posted on 2010-01-09
8
Medium Priority
?
231 Views
Last Modified: 2012-05-08
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
0
Comment
Question by:Steve_Brady
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 11

Assisted Solution

by:rajvja
rajvja earned 400 total points
ID: 26273536
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 26273680
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
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 26273684

replace

TimeValue("00:00:05")

with

TimeValue("00:02:00")

for two minute gap

Kris
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Steve_Brady
ID: 26294559
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
 
LVL 18

Accepted Solution

by:
krishnakrkc earned 1600 total points
ID: 26294882
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
 

Author Comment

by:Steve_Brady
ID: 26295377
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
 

Author Comment

by:Steve_Brady
ID: 26296450
For your "Insert" step after Ctrl+F11, is that the Insert tab or something else?  I'm in Excel 2007.
0
 
LVL 18

Assisted Solution

by:krishnakrkc
krishnakrkc earned 1600 total points
ID: 26299856
Hi,

Insert Module.

For a tutorial on VBA see the link below.

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

Kris
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question