Todays Date keeps changing

fahadshareef

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,402
Office Version
  1. 365
Platform
  1. Windows
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.
 

fahadshareef

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 26, 2007
Messages
6,141
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Control plus Semi colon will produce the current date hard coded.
 

fahadshareef

New Member
Joined
Dec 25, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
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
 
Solution

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,402
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,972
Messages
5,621,908
Members
415,865
Latest member
pxmike

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top