Unexpected behaoivr from my if statement- Guidance needed,

[tewellt]

I have a spreadsheet where I pull data from incoming email notices from our jobs running. In one cell I have a formula that insert today's date when another cells in the row is populated. This works for the day that the new row is populated. However the next day when the Excel Workbook is opened the previous days dates are reset to today. Cell has the following formula

=If($D5,"","",Today())

So on 10/12/17 cell B5 is populated with 10/12/2017. I save and close the workbook, The next day I open the workbook to process the days notices. All the rows populated yesterday now have todays date. (12/13/2017).

Can any shed any light on why this is happen. I am using 2013 Office suite.

 

[FDibbins]

TODAY() is dynamic, it reflects the date as of today, and updates as the date progresses. There is no real way around that, other than manually entering today's date (a shortcut for that is ctrl ; = date)

 

[Eric W]

I know 2 ways around this, and they both have their drawbacks.

First way is to go to the File tab > Options > Formulas > Calculation Options > and check "Enable iterative calculation".

Now assuming your formula is in B5, enter this formula:

=IF($D5<>"",IF($B5="",TODAY(),$B5),"")

You'll notice that the formula actually refers to itself, creating a circular reference, which is usually a no-no. But since iterative calculations is enabled, it will only go through the process 100 times (which was the default on the setup page, you can reduce it to improve performance). Now when something is entered in D5, B5 will be populated with the date, and it won't change when you open the book again. But it will clear out if you clear out D5. Downsides include "what happens if you open this workbook in a version of Excel without that option enabled?"


The second option is to write an event handler. Open a copy of your workbook. Right click on the sheet tab on the bottom and select View Code. Paste this code into the window that opens:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("D5:D50")) Is Nothing Then Exit Sub
    
    Target.Offset(, -2) = Date

End Sub

Change the range to the cells that you're monitoring. The -2 in red refers to the column 2 before the D range.

Now close the editor with either the red X, or Alt-Q. Now when you enter anything in the specified range, the date will be added in the same row, column B. Downsides to this method is that it makes your workbook macro-enabled, and some people are paranoid about that (justifiably). But it's probably the better option.

Hope this helps.