Time update

[gusdafa]

Hi there,

I have a formula that updates the time wherever data is entered in the
B15 cell. I copied this formula down the column so that each update
(B16..B17) will have its on time update, the problem is that the latest
time update updates all the previous times to the current time. Can
someone help me rectify this please? I want times updated previously to
be locked and not updated anymore. I have tried locking nearly
everything in the formula but got thrown errors.

Here is the formula,
=IF(B15<>"",TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())),"")

Thanks.

 

[Dave Peterson]

First, your formula can be simplified to:
=IF(B15<>"",NOW(),"")

But the bad news is that this formula will reevalate each time excel recalcs.

You could use enter the date and time by using a couple of shortcut keys:
ctrl-; (spacebar) ctrl-:

Or you could use an event macro that populates the cell when B15 (and more)
change. See JE McGimpsey's site:
http://www.mcgimpsey.com/excel/timestamp.html

 

[Max]

Try JE McGimpsey's coverage of "Time and date stamps" at his page:
http://www.mcgimpsey.com/excel/timestamp.html
Scroll down to the part: Using a worksheet event macro
(You can't use formulas as the "date/time stamps" as these will recalc)

 

[David Biddulph]

The formula does not simplify to =IF(B15<>"",NOW(),"") but to
=IF(B15<>"",MOD(NOW(),1),""), but Dave is right that it would recalculate
each time.

 

[T. Valko]

Scroll down to the part: Using a worksheet event macro

You can't use formulas as the "date/time stamps" as these will recalc

You scrolled right past "Using circular references and worksheet functions".

 

[Max]

Intent was to inform OP to try this part in JE's page:
---

 

[Rick Rothstein \(MVP - VB\)]

First, your formula can be simplified to:

The formula does not simplify to =IF(B15<>"",NOW(),"") but to
=IF(B15<>"",MOD(NOW(),1),"")

Or even... =IF(B15<>"",NOW()-TODAY(),"")

Rick

 

[Dave Peterson]

You're right.

Thanks for the correction.

The formula does not simplify to =IF(B15<>"",NOW(),"") but to
=IF(B15<>"",MOD(NOW(),1),""), but Dave is right that it would recalculate
each time.

 

[Dave Peterson]

And thanks for alternative, too!

The formula does not simplify to =IF(B15<>"",NOW(),"") but to
=IF(B15<>"",MOD(NOW(),1),"")

Or even... =IF(B15<>"",NOW()-TODAY(),"")

Rick

 

[gusdafa]

Try JE McGimpsey's coverage of "Time and date stamps" at his page:
http://www.mcgimpsey.com/excel/timestamp.html
[/i]

[/QUOTE]
Thanks a lot for the help.

Problem resolved.