Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a cell
C. Problem is when I enter "s" into another cell excel updates all the "s"
cells to the current time. I do not want it to update all the cells to
current time, just record the current time at the time I enter "s"...
I know there is a way by using Macro but is that also possible using just a
formula?? (like above)

Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a
cell C. Problem is when I enter "s" into another cell excel updates
all the "s" cells to the current time. I do not want it to update all
the cells to current time, just record the current time at the time I
enter "s"... I know there is a way by using Macro but is that also
possible using just a formula?? (like above)

Here's one way.

First check the checkbox at
Tools Options Calculation Iteration
to allow circular references.

Then use in D5:
=IF(C5="s",IF(D5="N/A",NOW(),D5),"N/A")

<but is that also possible using just a formula?

essentially, no.

Look he

http://www.mcgimpsey.com/excel/timestamp.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"M.ROD" wrote in message ...
| Enter "s" to record
| B C D
| call # timestamp timestamp
| 1 s 1:34:29 PM
| 2 s 1:34:29 PM
| 3 N/A
| 4 N/A
| FORMULA: =IF(C5="s",NOW(),"N/A")
| I would like capture the current time in cell D when I enter "s" in a cell
| C. Problem is when I enter "s" into another cell excel updates all the "s"
| cells to the current time. I do not want it to update all the cells to
| current time, just record the current time at the time I enter "s"...
| I know there is a way by using Macro but is that also possible using just a
| formula?? (like above)
|
|

You can use a formula that uses a circular reference.

Before you enter the formula set iteration:

Goto the menu ToolsOptionsCalculationcheck IterationOK

Now enter the formula(s):

In D5:

=IF(C5<"s","N/A",IF(D5="N/A",NOW(),D5))

Format as desired.

Copy down as needed.

--
Biff
Microsoft Excel MVP


"M.ROD" wrote in message
...
Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a cell
C. Problem is when I enter "s" into another cell excel updates all the "s"
cells to the current time. I do not want it to update all the cells to
current time, just record the current time at the time I enter "s"...
I know there is a way by using Macro but is that also possible using just
a
formula?? (like above)

This formula works great..but if I want to go back and re-enter "s" in the
cell it will not update the time. How can I do that?

"MyVeryOwnSelf" wrote:

Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a
cell C. Problem is when I enter "s" into another cell excel updates
all the "s" cells to the current time. I do not want it to update all
the cells to current time, just record the current time at the time I
enter "s"... I know there is a way by using Macro but is that also
possible using just a formula?? (like above)

Here's one way.

First check the checkbox at
Tools Options Calculation Iteration
to allow circular references.

Then use in D5:
=IF(C5="s",IF(D5="N/A",NOW(),D5),"N/A")

Delete the original "s" first then re-enter the "s".

--
Biff
Microsoft Excel MVP


"M.ROD" wrote in message
...
This formula works great..but if I want to go back and re-enter "s" in the
cell it will not update the time. How can I do that?

"MyVeryOwnSelf" wrote:

Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a
cell C. Problem is when I enter "s" into another cell excel updates
all the "s" cells to the current time. I do not want it to update all
the cells to current time, just record the current time at the time I
enter "s"... I know there is a way by using Macro but is that also
possible using just a formula?? (like above)

Here's one way.

First check the checkbox at
Tools Options Calculation Iteration
to allow circular references.

Then use in D5:
=IF(C5="s",IF(D5="N/A",NOW(),D5),"N/A")

Ok, thanks everyone this helped me alot!

"T. Valko" wrote:

Delete the original "s" first then re-enter the "s".

--
Biff
Microsoft Excel MVP


"M.ROD" wrote in message
...
This formula works great..but if I want to go back and re-enter "s" in the
cell it will not update the time. How can I do that?

"MyVeryOwnSelf" wrote:

Enter "s" to record
B C D
call # timestamp timestamp
1 s 1:34:29 PM
2 s 1:34:29 PM
3 N/A
4 N/A
FORMULA: =IF(C5="s",NOW(),"N/A")
I would like capture the current time in cell D when I enter "s" in a
cell C. Problem is when I enter "s" into another cell excel updates
all the "s" cells to the current time. I do not want it to update all
the cells to current time, just record the current time at the time I
enter "s"... I know there is a way by using Macro but is that also
possible using just a formula?? (like above)

Here's one way.

First check the checkbox at
Tools Options Calculation Iteration
to allow circular references.

Then use in D5:
=IF(C5="s",IF(D5="N/A",NOW(),D5),"N/A")