Time / Date stamping in a cell

biccy g

New Member
Joined
Oct 17, 2006
Messages
8
Hi All,

I am using the formula now() to put the date and time in a cell during a macro. Each day I run a new worksheet and save it, I went into yesterdays sheet and the now() dates times have all changed to literally now when I entered the spreadsheet. Is there anyway I can lock this time / date value, or does anybody know of a more sensible formula to use?

Many Thanks

:confused:
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

wongm003

Board Regular
Joined
Aug 8, 2005
Messages
237
I've made template workbooks that I use and macros are run in ThisWorkbook Workbook_Open event to prevent the macros being run after the workbook is saved, I initially save the template workbook with "template" in the file name and when the file is saved take out the template... You can use any naming convention that you like.

Code:
Private Sub Workbook_Open()

    If ActiveWorkbook.Name Like "*template*" Then
        'run macros/perform code
        Range("A1").Value = Now()
    End If

End Sub
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,882
Office Version
  1. 365
Platform
  1. Windows
You say you're using the "formula" NOW().......... well if you put that FORMULA into a cell, every time that the sheet calculates it will update the contents of the cell.

If you use the "FUNCTION" NOW(), then the contents of the cell in question should remain static - AS LONG AS YOU DON'T KEEP RUNNING YOUR MACRO ON THE SAME SHEET.
Code:
Sub enter_date()
Me.[A1].Value = Now()
End Sub
This code, using the ME keyword will only work if you put it into the code module of the sheet in question, otherwise use the long reference to the worksheet.
 

Forum statistics

Threads
1,141,219
Messages
5,705,086
Members
421,378
Latest member
CarlosDuran

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top