Stop the automatic update of a date column

Microsoft Excel 2003 (full product)
August 4, 2010 at 07:33:01
Specs: Windows XP
I am using excel to develop a stock management sheet. The worksheet (called Action Sheet) is laid out with code(A), name(B), date(C), action(D), user(E) and quantity(F & G).

The name column uses the cell formula [=LOOKUP('Action Sheet'!A:A,'Chemical Lookup'!A:B)] to update itself from another worksheet (Chemical Lookup) when an entry is made in the code column.

I want to a similar task in the date column [=IF(A2>1,$I$1,"")] (I1 is =TODAY()) when an entry is made but want the date to remain static for the entry i.e. I do not want the date to change when I am making further entries on a different date.

Please help as this is beginning to drive me nuts!


See More: Stop the automatic update of a date column

Report •


#1
August 4, 2010 at 08:27:24
Hi,

You can't do that with a formula.
Depending on your exact requirements, it is likely possible with some Visual Basic code.

To clarify:
When a code is entered in Column A and the code value is greater than 1, you want a date stamp in the same row in Column C.
Question 1:
Will the value entered in column A ever be changed, and If so do you want a new date stamp.
Question 2:
Why is your lookup on the value in column A based on a value greater than 1.
What is in the cells in column A before you enter a valid code number.

Regards


Report •

#2
August 4, 2010 at 08:32:19
Hi,

As a follow-up, TODAY() is one of Excel's 'volatile' functions.
It updates every time any data is changed or the workbook is opened or closed, (unless certain options have been turned off, such as Calculation is set to Manual).

Even if you set Calculation to manual, you will have to recalculate at some time and the date will change.

Referencing TODAY() in another cell does not help, because Excel keeps track of all cell dependencies, and when the cell containing TODAY() changes, so does every cell linked to it.

A Visual Basic macro can take the date and put it in a cell as a value, with no formula, and hence no link to the Today function.

Regards


Report •

#3
August 4, 2010 at 09:02:30
Hi Humar,

Thanks for getting back to me so quickly! I was begining to think that a formula was not going to do this for me.

You are correct that when a code is entered in column A a date stamp will be entered in the same row in column C. In answer to question 2 first, the formula is based on A>1 as the cell in column A is blank and I do not want a date entry in the row unless values for the full row are being entered (ie a stock in/out entry)

The value entered in column A should never change (unless an entry error has been made - the whole row would be removed), but even if a correction was to be made, I would want the original date stamp to remain valid.

I appreciate that the TODAY() function is a volatile one but was hoping beyond hope that there was another function or formula available that would easily do the job for me without having to get into a VB macro.

Just to throw something else into the mix... I already have a VB macro in the worksheet for the controlling of the stock in/out entries using pop-up boxes and a barcode scanner but don't see this having any major complications for trying to solve my date issues.

Whilst I have managed to sort out the data entry macro, my programming skills are seriously limited hence my plea for help on the date stamp!

Any and all help you can give me would be very much appreciated!

Mark


Report •

Related Solutions

#4
August 4, 2010 at 10:18:40
Hi,

Right-click the name tab of the worksheet and select 'View Code"
Enter this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
    'disable events to stop changes made by this macro re-trigering it
    Application.EnableEvents = False
    'test if changed cell is a number
    If IsNumeric(Target.Value) Then
        'only add a date if no date in column I
        If Not IsDate(Range("I" & Target.Row).Value) Then
            Range("I" & Target.Row).Value = Format(Date, "dd/mmm/yy")
        End If
    End If
    're-enable events
    Application.EnableEvents = True
End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

Now when a number is entered in a cell in column A, today's date is entered into the cell in column I on the same row.

As it is possible to select a cell already containing a valid number and hit Enter - which would trigger this code, there is an additional test - a new date is only entered if there is not a valid date in column I already.

You can change the date format used in the code.

Regards


Report •

#5
August 5, 2010 at 01:58:59
Thanks Humar,

Can I just check a few things with you... My date column is C so I have entered "C" where you have referenced "I"

The changed cell test is based on "IsNumeric" - the code being entered in column A is alphanumeric (i.e.: A0131, A0376, B0518, etc) does this make a difference?

The date column had the cell formula [=IF(A2>1,$I$1,"")], where I1 is (=TODAY()) - should this formula remain in the cell or should the cell be blank?

I really appreciate all your help!


Report •

#6
August 5, 2010 at 05:30:41
Hi,

Sorry about that (I instead of C)

Here is the modified code - date in column C and an alphanumeric in column A.

Any data entered into column A puts a date in column C on the same row, unless there is already a date in column C.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ErrHnd:
If Target.Column = 1 Then
    'disable events to stop changes made by this macro re-trigering it
    Application.EnableEvents = False
    'only add a date if no date in column C
    If Not IsDate(Range("C" & Target.Row).Value) Then
        Range("C" & Target.Row).Value = Format(Date, "dd/mmm/yy")
    End If
    're-enable events
    Application.EnableEvents = True
End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
're-enable events
Application.EnableEvents = True
End Sub

The date column should be blank (no formula required) and nothing is needed in column I

The macro will handle all the date requirements.

Regards


Report •

#7
August 5, 2010 at 06:42:42
Humar... you are a genius!

It works perfectly!

For some reason the date format is coming out in American style (mm/dd/yy) but I'm not bothered about it.

Thanks again!


Report •

#8
August 5, 2010 at 09:06:49
Hi,

Glad to hear it works OK.

The date column is possibly already formatted as mm/dd/yy, and the code is not actually changing the format.

Add an extra line:

    If Not IsDate(Range("C" & Target.Row).Value) Then
        Range("C" & Target.Row).Value = Format(Date, "dd/mmm/yy")
        Range("C" & Target.Row).NumberFormat = "dd/mmm/yyyy"
    End If
and see if that works.
or just select the date column and set a suitable date format.

Regards

Humar


Report •

#9
August 14, 2010 at 08:15:11
this worked perfect for what I needed as well. I have one question. How can I add the time as well to the date?

Report •

#10
August 14, 2010 at 10:18:41
Hi,

Use Now
This provides date and time information,
then format the cell with a date & time format.

Using the previous example:

        Range("C" & Target.Row).Value = Now
        Range("C" & Target.Row).NumberFormat = "dd/mmm/yy hh:mm"

Regards


Report •

#11
December 29, 2010 at 00:03:51
i have a query about dates can i shoot it?

Report •

#12
December 29, 2010 at 00:05:30
I have a column that is supposed to store status...
my requirment is that when i change the status to "Closed" then automatically the date should appear in the next column.
i tried using today and now function but issue with this is that if one record is changed all dates get reflected by same date

Report •

#13
December 29, 2010 at 00:12:45
I have a column that is supposed to store status...
my requirment is that when i change the status to "Closed" then automatically the date should appear in the next column.
i tried using today and now function but issue with this is that if one record is changed all dates get reflected by same date


I tried the following but it is not helping
=IF(H3="Resolved",+NOW(),O3)


Report •


Ask Question