Formula for Now() that doesn't update dynmically

[noslenwerd]

Hello,

I have the below formula which work to determine the date a case was closed, but the issue is when using Now(), is always updates to the current date.

=IF(OR([@Status]="Closed: Call complete",[@Status]="Closed: No Call"), NOW(),"")

For instance someone changed the case status to closed on 2/27/21, but it is showing as today's date (3/1/21)

Any work arounds?

 

[Joe4]

The NOW() function will always return the current date/time.
You can use VBA to add a static date/time stamp.

You can use Event Procedure VBA code to automatically update one column based on the update of another column.

 

[noslenwerd]

The NOW() function will always return the current date/time.
You can use VBA to add a static date/time stamp.

You can use Event Procedure VBA code to automatically update one column based on the update of another column.

Thanks for the reply. Unfortunately macro enabled documents are disabled by our enterprise. Trying to find a formula that can work around this.

 

[Joe4]

Have a look here and see if this works for you: Add a Date/Time stamp to your Excel worksheets

 

[Eric W]

You can try the iterative calculation trick. Click File > Options > Formulas > check the Enable iteratiive calculation box, and set the Maximum Iterations to 1.

Now use a formula like this:

Book1
	K	L
1	Status	Close date
2	Closed: No call	3/2/21
3	Closed: Call complete	3/2/21
4	c	1/0/00
Sheet5

Cell Formulas
Range	Formula
L2:L4	L2	=IF([@[Close date]]<>0,[@[Close date]],IF(OR([@Status]={"Closed: Call complete","Closed: No call"}),NOW(),0))

As you can see, the formula refers to the cell it is in, which is where the iteration comes in. This works on my tiny test sheet, but you may want to test it in more circumstances. Closing/reopening the file, opening on the next day, sending the file to someone who has not set the iterative calculation box, etc.

But really, using VBA to insert the date is better since it avoids some of these issues. If that's not an option, then you might have to train people to use Control+; (hold the Control key down and press the semi-colon key) to insert the date when they change the status.