Time update
 

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.




--
gusdafa

Time update
 

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

gusdafa wrote:

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.

--
gusdafa

--

Dave Peterson

Time update
 

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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"gusdafa" wrote in message
...

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.




--
gusdafa

Time update
 

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.
--
David Biddulph

"Dave Peterson" wrote in message
...
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

gusdafa wrote:

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.

--
gusdafa

--

Dave Peterson

Time update
 

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".


--
Biff
Microsoft Excel MVP


"Max" wrote in message
...
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)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"gusdafa" wrote in message
...

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.




--
gusdafa

Time update
 

Intent was to inform OP to try this part in JE's page:
Scroll down to the part: Using a worksheet event macro
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"T. Valko" wrote in message
...
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".

Time update
 

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

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" wrote in message
...
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

gusdafa wrote:

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.

--
gusdafa

--

Dave Peterson

Time update
 

You're right.

Thanks for the correction.

David Biddulph wrote:

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.
--
David Biddulph

"Dave Peterson" wrote in message
...
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

gusdafa wrote:

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.

--
gusdafa

--

Dave Peterson

--

Dave Peterson

Time update
 

And thanks for alternative, too!

"Rick Rothstein (MVP - VB)" wrote:

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

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" wrote in message
...
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

gusdafa wrote:

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.

--
gusdafa

--

Dave Peterson



--

Dave Peterson

Time update
 

Max;2642773 Wrote:
Try JE McGimpsey's coverage of "Time and date stamps" at his page:
http://www.mcgimpsey.com/excel/timestamp.html
[/i][/color]
Thanks a lot for the help.

Problem resolved.

:D




--
gusdafa