CODEWITHSUNDEEP
exceltimestamp
user420667
user420667

Reputation: 6700

Static timestamp in excel

I'd like to be able to add a timestamp at the click of a button to an excel cell. Unfortunately doing =Now() will always update to the current time, and thus is not static.

I could do F5 in notepad, and then transfer it to excel, but i'd rather cut out the middle man.

Thanks.

Upvotes: 1

Views: 35428

Answers (4)

Filip
Filip

Reputation: 31

Since NOW() and TODAY() cannot be static, the only way I found was to convert the cell with the formula to a static value. This can be done by means of a macro. The macro can then be run with a shortcut or button.

More interestingly, it can even be automated (e.g. when closing the file, every 15 min,...). If these macros are automated, they should be created in ThisWorkbook (VBA object).

Formula (in B1): =IF(A1<>"";TODAY();"")

' macro to convert a cell formula to a static value
Sub makeStatic()
    Set rng = Sheets("Sheet1").Range("B1:B500")  ' select the range
    For Each cell In rng 
        If cell.Value <> "" Then  
            cell.Formula = cell.Value
        End if
    Next cell
End Sub

' macro to automate this at shutdown
Sub Workbook_BeforeClose(cancel As Boolean) 
    makeStatic
End Sub

' or to automate this at a specific time interval (e.g. 15 minutes)
Sub StaticDataEach15min()
    Application.OnTime Now + TimeValue("00:15:00"), "makeStatic"
End Sub

Upvotes: 0

JimM
JimM

Reputation: 21

None of the examples I have seen really simply do what is asked. (Note Using Excel 2010)

Here is a simple macro. I saved the worksheet macro enabled and I added a QuickAccess Toolbar button to run it.

Sub MyTimeStamp()
Dim DT
'
' MyTimeStamp Macro
'
' Keyboard Shortcut: Ctrl+t
'

DT = Format(Now, "mm/dd/yyyy hh:mm:ss AM/PM")
ActiveCell.Select
Selection.NumberFormat = "mm/dd/yyyy hh:mm AM/PM"
ActiveCell.Value = DT

End Sub

Upvotes: 2

danielpiestrak
danielpiestrak

Reputation: 5439

Create a button next to a cell.

Then for the VBA code inside of that button's "onclick" event do something like this:

Sheets("Sheetname").Range("C1").value = time()

Or for date & time

Sheets("Sheetname").Range("C1").value = Now()

Either way only when the button is clicked will the value of that cell be set to the current time.

Note:

If you've never inserted a button object into excel here is a step-by-step guide for Excel 2003,2007, and 2010

http://www.excel-vba-easy.com/vba-how-to-create-macro-excel.html#create-macro-excel-2003

Update:

If you only want to add the timestamp to the currently selected cell then use the following line of code:

ActiveCell.value = Now()

Upvotes: 3

datatoo
datatoo

Reputation: 2049

You might not really need a button if you just use Ctrl+Shift+: help says Ctrl+: but don't forget to use Shift to get that.

Upvotes: 2

Related Questions