Need a formula to place todays date in cell when other cell ="closed"

**mrgillus** · 08-25-2009, 11:01 AM

I think the title pretty much describes it.. Basically in one column I have the status. Once the status in cell A2 is set to closed, then I want another corresponding cell, in my example B2 to display todays date. However, I want that date to be permanent. In other words I don't want it to continually update day after day. I just want it to put in the date that Closed is selected from cell A2.

Let me know you have any more questions! Hopefully its not too confusing..

I fiddled around with a formula in my example sheet but it didn't work..

Thanks for the help!

**DonkeyOte** · 08-25-2009, 11:13 AM

If you want to use Functions you will need to enable Iteration (tools - options)

Using your example file and with Max Iteration set to 1

B2: =IF(A2="CLOSED",IF(B2="",TODAY(),B2),"")
copied down

Once CLOSED the date stamp is entered and will remain fixed until such time as A is reset.

**mrgillus** · 08-25-2009, 11:49 AM

=IF(A3="CLOSED",IF(B3="",TODAY(),B3),""))

I tried your code in my example book minus one of the paranthesis at the end but I couldn't get it to work. I ran the evaluate formula thingy and it showed that B2 was equalling zero instead of "", which caused it to keep evaluating out to "".

I changed all of the "" to zeros in the formula and it appears to be working now.. Let me know if there could be any unforseen problems to this method.

=IF(A2="CLOSED",IF(B2=0,TODAY(),B2),0)

THanks for your help!

**DonkeyOte** · 08-25-2009, 11:53 AM

Yes, parentheses is an error.

See the attached "proof of concept" - note

1 - Iteration should be set to 1

2 - I used NOW rather than TODAY to illustrate it working - ie you can see that as other volatile actions are performed (ie invoking a calculation) the datetime stamp once entered does not recalculate unless the status is reset from Closed.