I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

To time stamp a cell with the current time without it updating constantly, follow these steps:

1. Select the cell where you want to insert the time stamp.
2. In the formula bar, type
   Formula:

   "=NOW()" 

   (without the quotes) and press Enter. This will insert the current date and time in the cell.
3. Right-click on the cell and select Copy from the context menu.
4. Right-click on the cell again and select Paste Special from the context menu.
5. In the "Paste Special" dialog box, select "Values" and click "OK". This will replace the formula with the current time value, which will not update automatically.

Now, the cell will display the current time when you first enter the formula, but it will not update automatically. If you want to update the time stamp, you can simply repeat the steps above to insert a new time stamp.

You can do that via a VBA event macro, but first you have to specify
1. in what cell do you want to enter NOW
2. what is the event (e.g. filling in a particular cell) triggering time
stamping.

Regards,
Stefi

€žgoneil€� ezt Ã*rta:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

Ctrl plus the shift key plus the ; (semi-colon) (hit all 3 keys at the same
time) will return the current time in the cell and it will not be updated.

Please hit Yes if my comments have helped.

Thanks!

"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

Ctrl and the ; (semi colon) hit simultaneously will return the current date
to a cell and it will not be updated.

Please hit Yes if my comments have helped.

Thanks!

"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

From MS documentation:-

to insert the current date and time, press CTRL+; (semi-colon), then press
SPACE, and then press CTRL+SHIFT+; (semi-colon).

Please hit Yes if my comments have helped.

Thanks!



"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

To record the timestamp manually you can hit Ctrl+; followed by space bar and
followed by Ctrl+Shift+;

To record the timestamp automatically you cannot use a formula. You will
have to use a VBA solution. Select the sheet tab which you want to work with.
Right click the sheet tab and click on 'View Code'. This will launch VBE.
Paste the below code to the right blank portion. Get back to to workbook and
try out.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A1:A20")) Is Nothing Then
If Target.Count = 1 Then _
Range("C" & Target.Row) = Format(Now, "mmm dd, yyyy h:mm AMPM;@")
End If
Application.EnableEvents = True
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

Yes, but this solution doesn't meet the OP's requirement "without the data
entry operator having to do anything"!

Stefi


€žtrip_to_tokyo€� ezt Ã*rta:

Ctrl plus the shift key plus the ; (semi-colon) (hit all 3 keys at the same
time) will return the current time in the cell and it will not be updated.

Please hit Yes if my comments have helped.

Thanks!

"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

Try this.

Past the below code in Excel VBA Module and assign shortcut key for that.

Press ALT+F11 and go to Insert and click Module. And paste the below code.

Sub DateandTime()
ActiveCell = Now()
End Sub

After pasting the code close the VBA and press ALT+F8, the DateandTime Macro
will appear. Just select it and select Options and give your desired
keyboard button as shortcut key to run the macro.

If you want to show the cells including seconds just select the cells and
Goto Format and select custom and paste this format [$-409]m/d/yy h:mm:ss
AM/PM;@

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------




"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

My post will not solve your problem, since it needs to be done manually. So,
Pls ignore my post.

--------------------
(Ms-Exl-Learner)
--------------------



"Ms-Exl-Learner" wrote:

Try this.

Past the below code in Excel VBA Module and assign shortcut key for that.

Press ALT+F11 and go to Insert and click Module. And paste the below code.

Sub DateandTime()
ActiveCell = Now()
End Sub

After pasting the code close the VBA and press ALT+F8, the DateandTime Macro
will appear. Just select it and select Options and give your desired
keyboard button as shortcut key to run the macro.

If you want to show the cells including seconds just select the cells and
Goto Format and select custom and paste this format [$-409]m/d/yy h:mm:ss
AM/PM;@

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------




"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

Hi Stefi,

Thank you for your response, much appreciated. I think this may not be
possible and perhaps thats why I cant figure it out on my own (I also don't
know VBA) or with community help.

I have a spreadsheet that I want to record how much time I spend on each
call I make or receive. I need to do it efficiently because I may only spend
1 or 2 minutes on some calls or less but rounded up to a minimum 1 minute.

I want to simply enter the Duration of the call and Nothing Else:

1. Enter call Duration hh mm into separate cells, then populate other cells
by:
2. Get the Current Time
3. Subtract the call duration from Current Time
4. And that becomes my Start Time.

The problem is that the end time keeps updating because it gets the current
timestamp from NOW().

A B C D
Duration -----------Time---------
Hours Mins Start End
1 20 9:40 11:00
(NOW - Duration) (NOW)

Any ideas Stefi?
cheers
George

"Stefi" wrote:

You can do that via a VBA event macro, but first you have to specify
1. in what cell do you want to enter NOW
2. what is the event (e.g. filling in a particular cell) triggering time
stamping.

Regards,
Stefi

€žgoneil€� ezt Ã*rta:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

Install this Change event macro (first format columns C:D to the desired time
format):

Open VBA (Alt+F11)!
Right click on your worksheet name in the Project explorer window!
Select View code from the popup menu!
Copy/Paste macro code below in the code window!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <= 2 Then 'columns A and B
Range("C" & Target.Row) = Time - TimeSerial(Range("A" & Target.Row),
Range("B" & Target.Row), 0)
Range("D" & Target.Row) = Time
End If
End Sub

Regards,
Stefi


€žgoneil€� ezt Ã*rta:

Hi Stefi,

Thank you for your response, much appreciated. I think this may not be
possible and perhaps thats why I cant figure it out on my own (I also don't
know VBA) or with community help.

I have a spreadsheet that I want to record how much time I spend on each
call I make or receive. I need to do it efficiently because I may only spend
1 or 2 minutes on some calls or less but rounded up to a minimum 1 minute.

I want to simply enter the Duration of the call and Nothing Else:

1. Enter call Duration hh mm into separate cells, then populate other cells
by:
2. Get the Current Time
3. Subtract the call duration from Current Time
4. And that becomes my Start Time.

The problem is that the end time keeps updating because it gets the current
timestamp from NOW().

A B C D
Duration -----------Time---------
Hours Mins Start End
1 20 9:40 11:00
(NOW - Duration) (NOW)

Any ideas Stefi?
cheers
George

"Stefi" wrote:

You can do that via a VBA event macro, but first you have to specify
1. in what cell do you want to enter NOW
2. what is the event (e.g. filling in a particular cell) triggering time
stamping.

Regards,
Stefi

€žgoneil€� ezt Ã*rta:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

Great result Stefi, thank you.
George

"Stefi" wrote:

Install this Change event macro (first format columns C:D to the desired time
format):

Open VBA (Alt+F11)!
Right click on your worksheet name in the Project explorer window!
Select View code from the popup menu!
Copy/Paste macro code below in the code window!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <= 2 Then 'columns A and B
Range("C" & Target.Row) = Time - TimeSerial(Range("A" & Target.Row),
Range("B" & Target.Row), 0)
Range("D" & Target.Row) = Time
End If
End Sub

Regards,
Stefi


€žgoneil€� ezt Ã*rta:

Hi Stefi,

Thank you for your response, much appreciated. I think this may not be
possible and perhaps thats why I cant figure it out on my own (I also don't
know VBA) or with community help.

I have a spreadsheet that I want to record how much time I spend on each
call I make or receive. I need to do it efficiently because I may only spend
1 or 2 minutes on some calls or less but rounded up to a minimum 1 minute.

I want to simply enter the Duration of the call and Nothing Else:

1. Enter call Duration hh mm into separate cells, then populate other cells
by:
2. Get the Current Time
3. Subtract the call duration from Current Time
4. And that becomes my Start Time.

The problem is that the end time keeps updating because it gets the current
timestamp from NOW().

A B C D
Duration -----------Time---------
Hours Mins Start End
1 20 9:40 11:00
(NOW - Duration) (NOW)

Any ideas Stefi?
cheers
George

"Stefi" wrote:

You can do that via a VBA event macro, but first you have to specify
1. in what cell do you want to enter NOW
2. what is the event (e.g. filling in a particular cell) triggering time
stamping.

Regards,
Stefi

€žgoneil€� ezt Ã*rta:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

On Tuesday, September 29, 2009 7:29:01 AM UTC-4, Ms-Exl-Learner wrote:
Try this.

Past the below code in Excel VBA Module and assign shortcut key for that.

Press ALT+F11 and go to Insert and click Module. And paste the below code.

Sub DateandTime()
ActiveCell = Now()
End Sub

After pasting the code close the VBA and press ALT+F8, the DateandTime Macro
will appear. Just select it and select Options and give your desired
keyboard button as shortcut key to run the macro.

If you want to show the cells including seconds just select the cells and
Goto Format and select custom and paste this format [$-409]m/d/yy h:mm:ss
AM/PM;@

If this post helps, Click Yes!

--------------------
(MS-Exl-Learner)
--------------------




"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers



Worked like a charm, thanks

But its not formula.

On Tuesday, September 29, 2009 at 3:21:01 PM UTC+5:30, trip_to_tokyo wrote:
From MS documentation:-

to insert the current date and time, press CTRL+; (semi-colon), then press
SPACE, and then press CTRL+SHIFT+; (semi-colon).

Please hit Yes if my comments have helped.

Thanks!



"goneil" wrote:

I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers



There is not CTRL key in ipad.

On Tuesday, September 29, 2009 at 3:02:01 PM UTC+5:30, goneil wrote:
I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

I have a similar query. I want to generate a timestamp in Column C whenever someone enters any complaint in Column B.
So I have used this formula in all the cells of Column C
"=IF(B3="","",TEXT(NOW(),"dd/mm/yy/hh:mm"))"

But the problem is that the timestamp keep getting updated. I want to lock it down to the initial timestamp.
Can you suggest any code for this?
Thanks

--

<https://www.somaiya.eduÂ* Â* Â* Â* <http://www.somaiya-ayurvihar.orgÂ*
<http://nareshwadi.orgÂ* <http://somaiya.comÂ* <http://www.helpachild.inÂ*
<http://nareshwadi.org

On Tuesday, April 24, 2018 at 1:41:39 AM UTC-5, wrote:
On Tuesday, September 29, 2009 at 3:02:01 PM UTC+5:30, goneil wrote:
I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

I have a similar query. I want to generate a timestamp in Column C whenever someone enters any complaint in Column B.
So I have used this formula in all the cells of Column C
"=IF(B3="","",TEXT(NOW(),"dd/mm/yy/hh:mm"))"

But the problem is that the timestamp keep getting updated. I want to lock it down to the initial timestamp.
Can you suggest any code for this?
Thanks

--

<https://www.somaiya.eduÂ* Â* Â* Â* <http://www.somaiya-ayurvihar.orgÂ*
<http://nareshwadi.orgÂ* <http://somaiya.comÂ* <http://www.helpachild.inÂ*
<http://nareshwadi.org

Has anyone come up for a solution to this one? I would also like to do the same. I would like to enter a callers name into column C, then have the NOW time populate into column B. currently this works but it will update. I would like to know how to eliminate the updating.

On Tuesday, April 2, 2019 at 11:02:03 AM UTC-4, wrote:
On Tuesday, April 24, 2018 at 1:41:39 AM UTC-5, wrote:
On Tuesday, September 29, 2009 at 3:02:01 PM UTC+5:30, goneil wrote:
I want to time stamp a cell automatically with the current time (ie) without
the data entry operator having to do anything, however I don't want it to
update all the time I want it to just remain as is permenantly. I cannot
make NOW() stay the same.
Cheers

I have a similar query. I want to generate a timestamp in Column C whenever someone enters any complaint in Column B.
So I have used this formula in all the cells of Column C
"=IF(B3="","",TEXT(NOW(),"dd/mm/yy/hh:mm"))"

But the problem is that the timestamp keep getting updated. I want to lock it down to the initial timestamp.
Can you suggest any code for this?
Thanks

--

<https://www.somaiya.eduÂ* Â* Â* Â* <http://www.somaiya-ayurvihar.orgÂ*
<http://nareshwadi.orgÂ* <http://somaiya.comÂ* <http://www.helpachild.inÂ*
<http://nareshwadi.org

Has anyone come up for a solution to this one? I would also like to do the same. I would like to enter a callers name into column C, then have the NOW time populate into column B. currently this works but it will update. I would like to know how to eliminate the updating.

One solution without VBA is to use circular reference to stop the cell from calculating. For example, in cell A1:

=IF(A1=0, NOW(), A1)

But you have to have circular references turned out, which might slow down calculations and cause unintended consequences.

I think this will work if I am correctly understanding what is trying to be done:


Cells(1, 1).value = now()


This should time stamp cell A1 and not update. It would just need to be used as a macro or as part of a macro.