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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

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.