Todays Date keeps changing

[fahadshareef]

I have a worksheet where I track uncleared checks. Whenever I put a R in column I, I want today's date in column O.
I am using formula =IF(I2="r",TODAY(),"") it works but the next day when I open the worksheet the date changes.
How can I lock the date?

 

[JLGWhiz]

Yes, that will happen if you use =TODAY() in the cell, because when tomorrow comes, it will be TODAY. If You want to retain the initial date on the that sheet, Then you have to select the cell with the formula, click copy, then paste special as 'Value'. But then that cell will not change again until you either put the formula back in it, or just enter the current day's date manually.

 

[jasonb75]

Dates produced by the TODAY() function are always current, if it didn't change then it wouldn't be today's date, it would be yesterday's date (or another past day).

To lock the date in place once it has been entered you would need to use a circular reference and refer back to the cell holding the formula. First of all, you need to enable iterative calculation (see link below) then you need to change the formula to

=IF(O2="",IF(I2="R",TODAY(),""),O2)

Alternatively, you could enter the date into column O with Ctrl ; then use

=IF(O2<>"","R","")

to enter the R into column I.

 

[fahadshareef]

Dates produced by the TODAY() function are always current, if it didn't change then it wouldn't be today's date, it would be yesterday's date (or another past day).

To lock the date in place once it has been entered you would need to use a circular reference and refer back to the cell holding the formula. First of all, you need to enable iterative calculation (see link below) then you need to change the formula to

=IF(O2="",IF(I2="R",TODAY(),""),O2)

Alternatively, you could enter the date into column O with Ctrl ; then use

=IF(O2<>"","R","")

to enter the R into column I.

Hi,
I don't see the link below to enable iterative calculation.

 

[JLGWhiz]

A little explanation - The TODAY() function is called 'Volatile' because it reacts to any event on the worksheet and immediately checks the internal date tracker to see what day it is, and then returns that date to wherever it is called from. It always returns the current date based on your system setup. It changes values at midnight based on the 24 hour period in the time zone your computer is set to. If you want a static date in a cell, you have to either copy the cell with a formula producing the date and paste special Values, enter the date manually or use a macro to enter the date which will enter value only.

 

[alansidman]

Control plus Semi colon will produce the current date hard coded.

 

[fahadshareef]

Hi,
I don't see the link below to enable iterative calculation.

In which column do I enter the changed formula to
=IF(O2="",IF(I2="R",TODAY(),""),O2)

 

[JLGWhiz]

If you can use macros, then right click the sheet name tab, click 'View Code' in the pop up menu, copy and paste the macro below into the code window and save. Then when you enter "R" in column I the date will be entered in column O on the same row. The date will not change when the the day changes.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
    If Not Intersect(Target, Range("I:I")) Is Nothing Then
        If UCase(Target.Value) = "R" Then
            Range("O" & Target.Row) = Date
        End If
    End If
Application.EnableEvents = True
End Sub

 

[jasonb75]

In which column do I enter the changed formula to
=IF(O2="",IF(I2="R",TODAY(),""),O2)

Sorry, I forgot to paste the link.

Remove or allow a circular reference - Microsoft Support

Find and remove a circular reference. You can also learn about the circular reference warning message and iterative calculation in Excel.

support.microsoft.com

Formula goes into O2, then fill down same as your old formula.