asked on

add a date to an excel field upon data input in specific range

Guys, I am after some help, I am after some code that when you enter any data in a range cells A1 to A20 that the adjacent cell B1 to B20 adds todays date

so if I enter into cell A5 then B5 will add todays date then if I entered another value in cell A19 tomorrow then B19 will have that date.

But the other dates that are already there don't alter unless that field that already has data init gets altered.

Any Ideas

Ryan Chong

>>so if I enter into cell A5 then B5 will add todays date then if I entered another value in cell A19 tomorrow then B19 will have that date.

can you provide examples what values are you entered in column A and what are the expected results in column B ?

DarrenJackson

ASKER

Wow thanks for the fast response

The column A will always be a date 01/01/2015 as an example in column B it will return the date of the day it was entered
ie
A B
01/01/2015 22/10/2015
04/07/2014 21/10/2015 *** if this was entered yesterday ****

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DarrenJackson

ASKER

Wow thanks for the quick responses. This is perfect thankyou very very much

Ryan Chong

I have put "Entered Date" into column B and "Updated Date" into column C, in case you need it...

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim r As Range
    For Each r In Target
        If r.Column = 1 And r.Row > 1 Then
            'Updated date
            If r.Offset(0, 1) <> "" And r.Offset(0, 2) = "" Then
                r.Offset(0, 2) = Date
            End If
            'Entered date
            If r.Offset(0, 1) = "" Then
                r.Offset(0, 1) = Date
            End If
        End If
    Next
End Sub

Open in new window

28763359.xlsm

DarrenJackson

ASKER

Ryan thank you also. didn't see your post until I accepted the other.

But I very much appreciate your post

Thankyou

Subodh Tiwari (Neeraj)

You're welcome Darren! Glad I could help.