# Todays Date keeps changing

##### New Member
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?

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### JLGWhiz

##### Well-known Member
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

##### Well-known Member
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
Excel Formula:
``=IF(O2="",IF(I2="R",TODAY(),""),O2)``

Alternatively, you could enter the date into column O with Ctrl ; then use
Excel Formula:
``=IF(O2<>"","R","")``
to enter the R into column I.

##### New Member
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
Excel Formula:
``=IF(O2="",IF(I2="R",TODAY(),""),O2)``

Alternatively, you could enter the date into column O with Ctrl ; then use
Excel Formula:
``=IF(O2<>"","R","")``
to enter the R into column I.
Hi,
I don't see the link below to enable iterative calculation.

#### JLGWhiz

##### Well-known Member

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

##### Well-known Member
Control plus Semi colon will produce the current date hard coded.

##### New Member

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

##### Well-known Member
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.

VBA Code:
``````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

##### Well-known Member
In which column do I enter the changed formula to
=IF(O2="",IF(I2="R",TODAY(),""),O2)
Sorry, I forgot to paste the link.
Formula goes into O2, then fill down same as your old formula.

Replies
5
Views
122
Replies
2
Views
171
Replies
9
Views
252
Replies
1
Views
409
Replies
3
Views
41

1,127,171
Messages
5,623,170
Members
415,955
Latest member
Footballtend

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back