Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Refresh current Date & Time in Excel 2007

Posted on 2010-01-09
8
Medium Priority
?
239 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

575 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