Updating date automatically

[adamsm]

Hi anyone,

I need to get the worksheets date updated when day changes. What would be efficient way to do that?

Here is the current code that I'm using in ThisWorkbook Module of my Workbook.

Sheets("New").Range("M14,O11") = Date

Any help on this would be kindly appreciated.

Thanks in advance.

 

[Steeviee]

I'm not sure I understand what you are trying to update here.

=TODAY()

in a cell

is the obvious answer but I think maybe you're looking for something more complicated???

 

[adamsm]

What I'm trying to get is the workbook to update the date when the day changes without closing the workbook?

Will your formula do that?

 

[Steeviee]

What I'm trying to get is the workbook to update the date when the day changes without closing the workbook?

Will your formula do that?

This formula always gives what today's date is and so that cell will update to today. Do you mean maybe that you want the filename to change or something? Sorry if I'm being dense

 

[adamsm]

I mean would it change the date to the current date at 12:00 ? even if the workbook is not closed?

 

[Steeviee]

Yes, as long as the calculation is not set set to manual or something, I'm pretty sure it should update fine.

I have checked this with the NOW() function which is the same as TODAY() except that it includes the current time, and yes this does update live on the document even when it is open.

 

[adamsm]

Thanks for the help. The calculation is set to automatic. So according to you there are no worries.

 

[Steeviee]

You might want to put something like this:

Private Sub Workbook_Open()
Application.OnTime Now() + TimeValue("[COLOR=red]00:10:00[/COLOR]"), "calcsub"
End Sub

in the workbook module and then in a normal module

Sub calcsub()
Calculate
End Sub

This runs a calculation every 10 minutes - ensuring that the TODAY() cell is updated. You can adjust the time setting in red above

 

[ADAMC]

Even with calculation set to automatic a formula will not auto re calculate until the workbook has been closed and re opened or the spreadsheet re calculates.

In the case of =today() when the clock hits > midnight tonight the formula will not just update this will only re-calculate when the sheet is re calculated by entering a cell then pressing enter key.

Or by pressing F9 to do an auto update of the formula.

If you were going to come into work next day and change something on that sheet the formula would re calc...However if this isnt the case then you would need to force the update by pressing F9 or re calculting the sheet.

Cheers

 

[adamsm]

Thanks for the help. I do really appreciate it.