How to stop excel from updating dates.

Question

Hi there,&nbsp;&nbsp;I have a running order list that we keep track of what is ordered when and when things are received. we are using a check box to initiate when the item was received. my problem is that the when i go to update an item the previous dates update to the current date.&nbsp;&nbsp;How can i prevent the date in column H from updating to keep historical dates?&nbsp;example of the document and the formula i am using.&nbsp; thank you in advance&nbsp;

hansvogelaar · Answer

An alternative would be to use VBA code. This would require users to allow macros. Would that be OK?

hansvogelaar · Answer

Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (*.xlsm).
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Dim cel As Range
    Set rng = Intersect(Range("G2:G" &amp; Rows.Count), Target)
    If Not rng Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each cel In rng
            If cel.Value = False Then
                cel.Offset(0, 1).ClearContents
            Else
                cel.Offset(0, 1).Value = Date
            End If
        Next cel
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub
&nbsp;

hansvogelaar · Answer

Change
Set rng = Intersect(Range("G2:G" &amp; Rows.Count), Target)
to
Set rng = Intersect(Range("G2:G" &amp; Rows.Count &amp; ",O2:O" &amp; Rows.Count &amp; ",W2:W" &amp; Rows.Count &amp; ",AE2:AE" &amp; Rows.Count &amp; ",AM2:AM" &amp; Rows.Count &amp; ",AU2:AU" &amp; Rows.Count &amp; ",BC2:BC" &amp; Rows.Count &amp; ",BK2:BK" &amp; Rows.Count &amp; ",BS2:BS" &amp; Rows.Count &amp; ",CA2:CA" &amp; Rows.Count &amp; ",O2:O" &amp; Rows.Count), Target)

m_tarler · Answer

So your formula uses TODAY() so it will constantly update as long as the checkbox is true.

you could use a circular reference to prevent updates but would have to change the Excel options to allow circular references (e.g. set iterations = 1) then

=IF( G7 * (H7=""), TODAY(), H7)

BUT instead I would recommend just get rid of the checkboxes and just use the Date recieved and if there is a date there then it is recieved and if not then it is not. And add the shortcut to let others know how to easily enter that date (CTRL-;) so something like:

Date Recieved
( CTRL - ; )

and if you don't know, you can use ALT-ENTER to create that line feed inside that cell

Then it is nearly as easy to enter the date as it is to click the box and you don't have to worry about circular references and I would claim less likely to error by people accidentally clicking the wrong box and then no knowing how to or forgetting to undo that and hence having the wrong date listed.

evoss87 · Answer

unfortunately have a handful of people that is not the best with using Excel. the ( CTRL - ; ) i know very well, but others do not, even when i train them to use it. that is why i wanted to use the check boxes. the check boxes at least is a easy visual for some to scroll through and see what was received and what wasn't since items can take a while to come in.&nbsp;

Forum Discussion

How to stop excel from updating dates.

9 Replies

Resources