Formula for time, possible?

[M.ROD]

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)

 

[Niek Otten]

<but is that also possible using just a formula?>

essentially, no.

Look here:

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| 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)
|
|

 

[T. Valko]

You can use a formula that uses a circular reference.

Before you enter the formula set iteration:

Goto the menu Tools>Options>Calculation>check Iteration>OK

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.

 

[M.ROD]

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?

 

[T. Valko]

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

 

[M.ROD]

Ok, thanks everyone this helped me alot!

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