Solved How to add active date to IF formula

May 1, 2018 at 17:28:20
Specs: Windows 7
Forgive me if I explain poorly... very new to excel.

I am trying to get my IF formula to show the current date (date entered), once information has been entered in a cell. Here is what I tried.

=IF(E6="","N/A","=TODAY()")

cell E6 is a date, and once it is entered I want my If formula cell to populate the date the information was entered automatically.

I hope that makes sense.


See More: How to add active date to IF formula

Reply ↓  Report •

#1
May 1, 2018 at 20:23:58
✔ Best Answer
What you want to do makes sense, but the way you want to do it probably won't work.

The good news is that your formula is easy to fix.

=IF(E6="","N/A","=TODAY()")

You see those quotation marks around the TODAY() function? Quotation marks tell Excel to treat whatever is in between them as Text. My guess is that when you enter a value in E6, you are seeing the Text string =Today() in your formula cell, not the current date. Excel is not going to evaluate it as if it was a formula.

If you want to use an Excel function inside a formula, don't use the quotes and don't use the equal sign:

=IF(E6="","N/A",TODAY())

(You'll probably get a 5 digit number until you format the cell as a Date)

Now for the bad news.

The TODAY() function will indeed return the current date when a value is entered in E6. The problem is that the TODAY() function always returns the current system date. e.g If I enter =TODAY() in a cell right now, it is going to return May 1, 2018. However, as soon as midnight rolls around and the sheet calculates, =TODAY() is going to return May 2, 2018. The next day it will return May 3, 2018, etc. It doesn't lock in any given date, it always returns the date of the then current "today".

If you want to make the date permanent, then you have 2 options:

1 - A manual Copy...PasteSpecial...Values which will eliminate the entire IF formula and leave the date in the cell.

2 - Use a Worksheet_Change macro to place the current date, as a date, in the cell when a date is entered in E6.

For example, you are trying to put the current date into F6 when a date is entered in E6, then this code should work. When the Date function is used in a macro, the current date is placed in the cell as a date, not as a function. The only time the date in F6 will change is if someone edits E6 at a later date.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = ("$E$6") Then
       If IsDate(Target) Then
             Range("$F$6") = Date
      End If
    End If
End Sub

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


Reply ↓  Report •

#2
May 2, 2018 at 10:10:02
Wow, perfect! Thanks for much for taking the time to explain... well to teach me this in such a way that I easily understood!

Life saver!!! :)


Reply ↓  Report •
Related Solutions


Ask Question