Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Time Stamp Change

Hello,

I have a shared workbook that serves as a call log for several users. Currently I am having users skip rows so as not to use the same cells when making entries. I have a formula that automatically stamps the time that a call is logged ie: for row 110 (=IF(ISBLANK(F110),"",IF(D110="",NOW(),D110)) The problem that I am encountering is that when I delete out unused rows and update the shared workbook, the time stamp resets to the time that the shared workbook is updated. Any suggestions would be greatly appreciated


Post your answer or comment

comments powered by Disqus
How can I have a cell (or series of cells) with a time stamp that won't change every time I enter data into other cells? I want to be able to put a date in [A1] and have a time show up in [B1]. Another date in [A2] with a new time stamp in [B2] without it changing the time in [B1] And so on. Any help would be appreciated.

All the samples of time stamping i have seen is for stamping the cell net too it. I would like to time stamp row 1 and user stamp row 2 when row 3 is changed.

hello, i need to put a date stamp when a change is made in b3:b31 into e3:e31 for each row also i need to put a date stamp into g3:g31 when a change is made in F3:f31 i try to use 1 "worksheet-change" and it is fine once i use 2 i get Ambiguous Name Detected errors
any ideas (sorry if i have not 100% correct in this listing rules as this is my first time!!!
much apreciated
timothy...


	VB:
	
 Excel.Range) 
    With Target 
        If .Count > 1 Then Exit Sub 
        If Not Intersect(Range("b3:b31"), .Cells) Is Nothing Then 
            Application.EnableEvents = False 
            If IsEmpty(.Value) Then 
                .Offset(0, 3).ClearContents 
            Else 
                With .Offset(0, 3) 
                    .NumberFormat = "dd mmm yyyy hh:mm:ss" 
                    .Value = Now 
                End With 
            End If 
            Application.EnableEvents = True 
        End If 
    End With 
End Sub 
 
 
 
Private Sub Worksheet_Change(ByVal Target As Excel.Range) 
    With Target 
        If .Count > 1 Then Exit Sub 
        If Not Intersect(Range("f3:f31"), .Cells) Is Nothing Then 
            Application.EnableEvents = False 
            If IsEmpty(.Value) Then 
                .Offset(0, 1).ClearContents 
            Else 
                With .Offset(0, 1) 
                    .NumberFormat = "dd mmm yyyy hh:mm:ss" 
                    .Value = Now 
                End With 
            End If 
            Application.EnableEvents = True 
        End If 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I've got a speadsheet that will need a time stamp. The entries will be
going into A1:A10 and in B1:10 will be the time stamp. But all the
time stamps that I seen on this forum change the time again if I change
a entry in A1:A10. I need the times in B1:B10 to be hard coded so they
do change upon amendments in A1:10

Regards

Antony

--
AntonyY
------------------------------------------------------------------------
AntonyY's Profile: http://www.excelforum.com/member.php...o&userid=16690
View this thread: http://www.excelforum.com/showthread...hreadid=320467

I seen many posts on in this forum on time stamps. My problem this that
in a1:a10 are going to be the entries. I need upon each entry into each
cell a time stamp in b1:b10, but I need this to be hard coded so that if
I went back in a1and made a change it wouldn't change the time again in
b1.

Regards

Antony

--
AntonyY
------------------------------------------------------------------------
AntonyY's Profile: http://www.excelforum.com/member.php...o&userid=16690
View this thread: http://www.excelforum.com/showthread...hreadid=320465

I have multipal rows of data that are changed by multipal users. I need to
know when changes were last made to individual rows of information. If there
is no way to tell this is there a way to time stamp individual worksheets
when changes are made.

Thank You,
Chuck Blechle

I have a time stamp routine which writes the date to a cell in the same column as the active cell which is updated, using the worksheet change event under which the following code falls:


	VB:
	
 
     ' Do Nothing
Else 
    With ActiveCell 
        Set TimestamperCell = Cells(1, .Column) 
        TimestamperCell = Now 
    End With 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is that if, instead of clicking Enter, somebody clicks on another cell in another column then the worksheet change event fires for that cell (which it is supposed to!) and hence the date is written to a cell in a different column! Is there any way around this?

Thanks a lot .

I would like to create a macro that is activated when a spreadsheet is opened. The macro would take note of the time the spreadsheet was opened, the changes that are made and then the time the file is closed. These details would be saved onto a separate worksheet in the workbook. Each time the spreadsheet is opened, the macro would create new 'time stamps' and activity logs on the same worksheet.
Can anyone help me please?
Many, many thanks
Jon

Can someone please help me modify this code so that I select only one column triggers the time stamp update? For e.g. if i make any changes in column A, the date stamp is updated in the corresponding cell in column B. Basically, I am trying to narrow down to only one cell in the row, but it should work for any row in the sheet. The many tweaks i tried with this code did not help. Any help would be greatly appreciated.

please refer to the below post:
http://www.ozgrid.com/forum/showthread.php?t=106105&


	VB:
	
 Range) 
    If Target.Row > 1 Then Cells(Target.Row, "B") = Now() 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


The following code was supplied by Bill, but I want to do the same thing in B10:B164, where "x" is entered in B10 and the time stamp is entered in C10 & D10.
I tried copying the same script but had an Compile error message which said Ambiguous name detected: Worksheet_Change.
The name of the sheet is Sheet 1 (Main)


	VB:
	
 Range) 
     
    If Target.Cells.Count > 1 Then Exit Sub 
    If Not Intersect(Target, Range("M10:M164 ")) Is Nothing Then 'change your range on this line"
        If Target.Value = "x" Then 
            Target.Offset(0, 1).Value = Now 
        Else 
            Target.Offset(0, 1).Value = "" 
        End If 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Can someone please help be decode this and explain to me how this works exactly. I have tried a few different things and I still cant figure it out. I have even tried to look at the function and am still not sure.I believe it makes a time stamp but I am not sure how it is doing it

My goal is to figure out how to create a timestamp in one cell when something is inserted or changed in another cell. I have a formula that works, but I am looking for a VBA code to do the same thing.


	VB:
	
 
     
    If makeSound = "On" Then 
        Call sndPlaySound32("C:Program Filestrigger.wav", 1) 
    End If 
     
    SetTrigger = Now 
     
End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Earlier today I received a solution for having a row of cells grayed out once "x' was entered in M11.
Now I want to have a time stamp entered into cell N11 once "x" is entered into M11. This should not interfer with the above mentioned solution.
I have tried using the NOW() option by using the formula "=IF($M11="x",NOW(),"")". The problem is that each subsequent time "x" is entered the time changes to the Task Bar time. I want an "as is" time stamp. So if "x" is entered at 1:15 PM, then N11 should say 1:15 PM. Then when "x" is entered at 1:18 PM, in cell M12, then 1:18 PM is entered in N12.
I then want to apply the Format Painter to apply the solution to all the cells following below.
John.

Hi!

I searched all over the net for an answer, but couldn't find one.
I have a system of checkboxes and now I need a time stamp when the check box was marked TRUE. They ar linked to cells so the solution can be either linked to a cell or to a checkbox. Obviously I can't use Today or Now functions because they are refreshed. I found many solutions for time stamps when a change is made in a cell, but none of them worked for me. I tried altering them with no succsess. I would be very happy if the solution would apply to the whole column, not one cell and checkbox, because I have A LOT of checkboxes.

The checkboxes are in column B and I want the time(date) stamps in column C.

Hi

I am using Mr McGimpsey date time worksheet macro in a customer calling spreadsheet with a small change:


	VB:
	
 Excel.Range) 
    With Target 
        If .Count > 1 Then Exit Sub 
        If Not Intersect(Range("k3:k1000"), .Cells) Is Nothing Then 
            Application.EnableEvents = False 
            If IsEmpty(.Value) Then 
                .Offset(0, 1).ClearContents 
            Else 
                With .Offset(0, 1) 
                     '.NumberFormat = "dd mmm yyyy hh:mm:ss"
                    .Value = Worksheets("Sheet1").Range("l2").Value 
                End With 
            End If 
            Application.EnableEvents = True 
        End If 
    End With 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Cell l2 has the formula =TEXT(NOW(),"ddd mm mmm yy h:mm ") as I wanted a stamp with that date format and that is static and does not change in the offset cell and with the ability to add text to it.

I wanted a further capability which is why I am writing to see if you might be able to put me on the right track.

I would like to keep a history of the calls made in the offset cells (notes heading column).

The K column (Call Status heading) uses a data validation list with a drop down box: Call Status
cb
done
not int
engaged ect...

The first thing that I would to be able to do is to add the value in the call status cell to the end of the date in notes cell, according to the entry made in call status cell as follows:

call status -----notes
engaged --------Mon 09 Sep 05 19:31 - engaged

Then on each subsequent call to add to the notes cell - that is preserve the contents of the cell present but to add a line break and then record the time stamp with the value in the selected call status value:

call status------notes
no ans---------Mon 09 Sep 05 19:31 - engaged
--------------- Tue 10 Sep 05 12:00 - no ans

I hope that this makes sense. Is this possible to do this in Excel without too much trouble?

I wrote to Mr McGimpsey for his advice and I am waiting for his reply but I thought that some of your experts might also be able to help me with this problem. Thank you in advance for all assistance.

Jean

Howdy, Here is what I'm trying to do. I have a worksheet with employees names and their overtime hours worked that week. I would like to add some code to a button that will prompt for a password. If that password matches a predefiened password then lock a range of cells and place a time stamp next to the button and maybe change the button color of the button to Red.

Now if the person with the password wants to change the locked cells, they can click the button again and enter the password and the button will turn green so they can make the changes.

I would like the time stamp to be updated every time the cells are locked. Also wanted to have one button for every work week so about four buttons per worksheet.

Here is an example of the worksheet.

The range of cells I want to lock are the ones in bold.

I know it's specific but any help would be appreciated. I tryied copeing and pasting codes and the result was not very profecional looking

I need some help getting a Date & Time stamp in a range E3:E300 whenever there is a change to a cell to that range.

example of output:
09-26-08 02:00 PM - "example of change made"

I would like to create a time stamp on a spreadsheet such that when a
certain action occurs, I can simply tap on a cell (or a couple of
taps) to input the current time to the nearest second in a cell. While
I can use the =now() function to get the current time, it continually
updates, and all the =now() cells change (unsurprisingly) to the
current time. I want the time to remain the time I tapped the given
cell. Is there a convenient means to do this in Excel Mobile?

Thanks for your help.
Brian

Does anyone have a tip how to have entered a static date
and time stamp based upon a user entering anything in an
adjacent cell? I am looking to capture the time and date
of user data entries so I can track when those entries
are entered into different cells on a worksheet.

If I use the now() function to capture when an entry is
made, it doesn't get saved as a static entry, so the
value changes whenever the Enter key is hit. I have made
a macro that enters a date or time stamp into a cell, but
that means a user has to initiate the macro, which would
be an extra step and the user would likely forget to
invoke it. I wish to simplify it so the macro is envoked,
or a function is called whenever the spreadsheet is
updated (the Enter key is hit) but that the static time
and date don't get changed unless the cell with the data
entry is changed.

Thanks for any suggestions.

-George

I've created the following code to record Username and a Date/Time
Stamp in cells K1 & L1 (respectively) after a user makes any change to
a cell in the same row (A1 through J1).

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("A1:J1"), Target) Is Nothing Then Exit Sub
Range("K1").Formula = Format(User())
Range("L1").Formula = Format(Now(), "dd-mmm-yyyy hh:mm:ss am/pm")
End Sub

I need to repeat this code for approx 50 rows. Any advice on the most
efficient way to handle this is greatly appreciated.

Thanks,
Ken

Does anyone know how to "Time Stamp" a cell as data is
entered into an adjacent cell? I need it to be in
minutes and seconds, and to not change when the
spreadsheet is regenerated. The "NOW" function works
great, except that it updates.

Thanks for any help.

Hi Gang,

I looked for quite some time trying to figure this out on my own but
no luck. I created an inventory spreadsheet that takes a download from
Oracle11i of all transactions posted within a 24-hour period. The
downloaded information is on one worksheet and a second worksheet in
the same file uses formulas to count various types of transactions and
calculate cost from the download. In the download there are various
columns containing quantity transacted, from and to departments,
transaction types and a reference section for users to input comments
when they transact. Usually the file has about 3000 rows. All of these
files have a date time stamp of when the transactions were processed.
Example: "6/18/2007 23:50". I believe the default format is "special"
for the date but I use a macro to change the format from military time
to regular time. (The macro also cleans up the initial download, which
has a lot of duplicate information).

Problem:
Where I work there are two shifts, one starts at 5:45 am to 5:45 pm
(days) and the other is from 5:45pm to 5:45am (nights). What I don't
know how to do is count the number of transactions that occurred
within the shift time frames. I want to avoid using a pivot table if
possible to keep down file size. Is there a formula instead that will
count the specific number of transactions that occurred within my
given time frames? I tried using a count if formula, but noticed if
the data is a "custom" or "date" format the formula will not work.
Also if possible I want to avoid creating another column to compute
the formula with. I guess I'm just looking for a formula to count the
time stamps within the criteria using the original column.

Objective:
To count how many transactions were posted on day and night shifts.

Sorry for the long post, just wanted to give as much detail as
possible.

Deepest thanks all!

Sam

TRYING TO FOLLOW A PROCESS AND TIME STAMP AT EACH STEP IN PROCESS THAN TOTAL
TIME AND NOTE DIFFERENCE BETWEEN STEPS

Erik,

In the ThisWorkbook code module, use something like

Private Sub Workbook_Open()
Worksheets("Sheet1").Range("A1").Value = Now
End Sub

Change the sheet name and the cell reference to your needs.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

"Erik" > wrote in message
...
> I am trying to place a date/time stamp in one cell that is
updated when the sheet is opened. What is the best way to do
this?
> Erik

I know I probably did post my reply correctly to get a possible solution to
my problem. Any further help would be greatly appreciated.

Sorry, but I failed to mention the process correctly. If end user enters
data in any cell in column A beginning at cell a2, then I would like the
corresponding cell(same row) in column B to have a date/time stamp that would
not change when the date/time changes or when the file is saved and reopened.
Example: if user enters data in cell A2, then B2 would automatically display
date/time stamp. If user enters data in cell A3, then B3 would have a
date/time stamp...and so on..and so on. TIA.

-----------------------------------------------

Enter this code in the module of the concerned sheet.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range("B1") = Now()
End If
End Sub

Will change the date only when you change or enter value in A1
Mangesh

________________________________________

I'm using Excel 2003.

Is this possible?

If end user enters data in A1, can I add a date/time function that will
automatically be entered in cell B1, but not update when the date/time
changes or when the file is closed/opened? Using Today() or Now(), the
date/time in B1 updates when file is closed and then opened.

TIA


No luck finding an answer? You could always try Google.