Dynamic Today() in a formula on another cell working but I need it to not change tomorrow or later.

chill75

New Member
Joined
Dec 17, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I'm sure this has been answered as I've read similar post on this forum but none matched what I am doing exactly and I need to get this fixed quickly. I apologize if this is a repeat, but I have a simple formula on a cell that when the value of the cell is not longer empty, or an employee started to type characters in the cell, it puts today's date in the next cell on the same sheet. Kind of like auto populating a start date for the employee. The formula is working perfectly but the issue is I do not want the employee to have to do anything to the date manually and I do not want the date to change when the document opened in a few days. Currently when the document is opened again the next day the start dates all change to the next day's date. Is there a way in the formula to mark the value and static once generated or a better way to auto record a date in a field that will not change when reopened? Thank you in advance for any advice you can give.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

chill75

New Member
Joined
Dec 17, 2020
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Awesome. The third option might do just the trick. Thank you!!
 

chill75

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

ADVERTISEMENT

Well I had hope but as of this morning when I opened the document and the dates changed to today's date and did not stay yesterday's date so those did not work it seems.
 

pedro-egoli

Well-known Member
Joined
Apr 25, 2004
Messages
1,217
Office Version
  1. 2016
Platform
  1. Windows
As an experiment I used the formula "=today()" in A1.
I then copied A1 and Paste Special/value in A1.
The result was that A1 still shows the original date.
Decided to record these actions in a macro (named todaytest) and this is the macro
VBA Code:
Sub todaytest()
'
' todaytest Macro
'

'
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub
In case it helps you
 

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,240
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
As an experiment I used the formula "=today()" in A1.
I then copied A1 and Paste Special/value in A1.
The result was that A1 still shows the original date.
Decided to record these actions in a macro (named todaytest) and this is the macro
VBA Code:
Sub todaytest()
'
' todaytest Macro
'

'
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("A1").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub
In case it helps you

Does this help...


Sub todaytest()

Range(“A1”).value = Date

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,128,016
Messages
5,628,150
Members
416,296
Latest member
smartua

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