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:
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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,752
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,877
Members
409,668
Latest member
mitunsLax

This Week's Hot Topics

Top