How to stop TODAY() auto-updating in Excel

April 22, 2020 at 04:20:48
Specs: Windows 10
Hello,
I have set up and IF condition =IF(F3="Completed",TODAY()) however I want the date appearing in G3 to be the date the job was marked as completed not to automatically refresh to the current date. Is there a way to do this?
Thank you

See More: How to stop TODAY() auto-updating in Excel

Reply ↓  Report •

#1
April 22, 2020 at 06:18:57
3 methods come to mind, with #3 being my recommendation.

1 - Manually: Copy...PasteSpecial-Values

2 - Semi-automatically: Use a Macro to perform the Copy...PasteSpecial-Values operation. You'll need to chose something to trigger the macro, such as a shortcut-key combination, a manual change to a cell, workbook open/close, etc. There are a number of events that you can choose to trigger the macro.

3 - Use a macro that puts the hardcoded date in the cell when Completed is entered in F3.

With Methods 1 & 2, you will lose the formula, so you won't be able to easily "reopen" the job.

With Method 3, you could clear the date by putting something other than Completed in F3.

If you are interested in using Method 3, let us know how F3 gets populated with "Completed" - manually or via a formula.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
April 22, 2020 at 09:02:38
F3 is currently populated manually with the word Completed. This then creates the IF function to fill F4 in with todays date. What we are hoping for is to be able to fix that date at the moment Completed is typed in.

(various workers are marking work as complete as they get to it so we dont want people to be able to manually enter a date as they choose note the need for an IF function)

We are working off Office 365


Reply ↓  Report •

#3
April 22, 2020 at 09:52:33
re: "F3 is currently populated manually with the word Completed."

If F4 is dependent on Completed being in F3, I wouldn't leave it up to a manual entry. I'd use a Drop Down to prevent spelling errors, extra spaces, etc.

re: "(various workers are marking work as complete as they get to it so we dont want people to be able to manually enter a date as they choose note the need for an IF function)"

Unless you have the worksheet Protected and have locked F4, the IF function isn't preventing your users from manually entering a date. They can just type over it.

This macro will place the current date in F4 immediately after Completed is entered into F3. The date will be hard coded, such as 04/22/2020 and will not change.

Again, unless F4 is locked against user entry, they can still type over anything that is in the cell.

If you need the date to be deleted if Completed is removed, that can be done with a few more lines of code.

If you choose to protect the sheet, a few more lines of code will also be required in order for the macro to put the date in the cell.

If you need the additional code, let me know.

Private Sub Worksheet_Change(ByVal Target As Range)

'Determine if F3 was changed and now contains Completed
 If Target.Address = "$F$3" Then
  If Target = "Completed" Then
   
'If true, enter current Date in F4
   Range("$F$4") = Date
  End If
 End If
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
April 23, 2020 at 01:36:14
That's great thank you so much. Assuming there are multiple lines do i just remove the $ or would then copying down the formula into the following cells work?

Reply ↓  Report •

#5
April 23, 2020 at 07:17:46
I'm confused by your question. This is not a formula, it's a macro. It is not something that you put in a cell.

Before I address you "multiple cells" question, I need to ask: Do you know how to use the macro in your workbook?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#6
May 14, 2020 at 02:35:46
DerbyDad03 was kind enough to help me out with something similar a while back.
I had trouble "memorizing" dates in an entire column of =TODAY() formulas, which falls in the 3rd option DerbyDad offered in post #1.

So I needed memorized the date returned by =TODAY() in column A, each time any text was added in the adjacent cell in column B. If the text in that B column cell would be deleted, the macro would put back the =TODAY() formula in the adjacent cell in column A.

You can see the post and solution here:
https://www.computing.net/answers/o...

message edited by Mrrrr


Reply ↓  Report •

Ask Question