MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Updating values when the system date changes


Posted by Gene on December 04, 2001 8:21 AM

I have written Excel code that calculated dates and times, which are based off of the system date and time. I wanted to see if the spreadsheet would update a change when the new year comes, and so I changed the date - but the values in my spreadsheet remained constant.

My questions is:
Does Excel have a utility to automatically update values that are based on the system?


Posted by Damon Ostrander on December 04, 2001 1:08 PM

Hi Gene,

I assume you are using the Today() function to give the system's date. Unfortunately (at least in your case) the Today() function will not calculate or update automatically when the date changes, but only when a sheet calculation is called for via a cell dependency or manual recalculation.

If you want the workbook to update the date the instant it changes (i.e., at midnight), you will need to use the OnTime event to force a sheet recalculation to always be done at midnight. To do this, run this line of code:

Application.OnTime Date()+1, "RecalcSheet"

where this schedules the RecalcSheet macro to be run at 00:00:00 then next day. The RecalcSheet macro is just:

Sub RecalcSheet()
Worksheets("Sheet1").Calculate
End Sub

I hope this helps.

Happy computing.

Damon

Of course, if Excel is not running at midnight the recalculation cannot occur, so you should also set up Excel so it recalculates the sheet each time the workbook is opened by putting Worksheets("Sheet1").Calculate in the ThisWorkbook Open event. The OnTime command above should also be placed in the ThisWorkbook Open event so that it gets scheduled as soon as the workbook is opened.

Posted by LARRY TILLMAN on March 06, 2002 10:26 AM