Auto Input Today's Date

[enquirer]

I wish to create formula to automatically insert date and time for
each new record created in Excel 2003/2007 database.
I've tried the following in cell A6, so when I enter data into
Worksheet called INPUT cell B6 , the date and time is automatically
entered into A6 of the DATA worksheet, but it doesn't work: =IF(INPUT!
B6>1,=NOW(),"")

What am I doing wrong?

I am using Data Form for user input of new data and don't want the
user to input into a date field, so I have created INPUT worksheet
without date and DATA worksheet with all identical fields using INPUT
plus a date field in column A for each record.

Also my formula is true if cell B6 = >1, but B6 is a text cell so I
would ideally like a formula that looks to see if cell is populated or
not. Is this available?

Thanks enquirer

 

[Gord Dibben]

I would abandon the use of a formula which uses the function NOW().

NOW() is a volatile function so will not remain static.

i.e. will update every time a calculation takes place.

To get a static date entered you can use VBA sheet event code.

Right-click on the INPUT sheet tab and "View Code"

Copy/paste the following code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B of INPUT sheet
'the date/time will appear in column A of DATE sheet
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Me.Range("B" & n).Value <> "" Then
Sheets("DATA").Range("A" & n).Value = Now
End If
End If
enditall:
Application.EnableEvents = True
End Sub

If you do want to use the volatile NOW() function, see John McGimpsey's site for
a method using circular references.

http://www.mcgimpsey.com/excel/timestamp.html

Note also the VBA solution, which is similar to above event code.


Gord Dibben MS Excel MVP

 

[enquirer]

I would abandon the use of a formula which uses the function NOW().

NOW() is a volatile function so will not remain static.

i.e.        will update every time a calculation takes place.

To get a static date entered you can use VBA sheet event code.

Right-click on the INPUT sheet tab and "View Code"

Copy/paste the following code into that module.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B of INPUT sheet
'the date/time will appear in column A of DATE sheet
On Error GoTo enditall
    Application.EnableEvents = False
If Target.Cells.Column = 2 Then
        n = Target.Row
        If Me.Range("B" & n).Value <> "" Then
            Sheets("DATA").Range("A" & n).Value = Now
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

If you do want to use the volatile NOW() function, see John McGimpsey's site for
a method using circular references.

http://www.mcgimpsey.com/excel/timestamp.html

Note also the VBA solution, which is similar to above event code.

Gord Dibben     MS Excel MVP







- Show quoted text -

Hi Gord, thanks for your reply. I've never used vba before, but am
very keen to learn. I,ve done as instructed above, but nothing seems
to happen.
I see your code mentions 2 columns, so to test again I have created
new workbook containing 2 worksheets:
INPUT - 2 columns - User and Information
DATA - 2 columns - Date and Information.
I've right clicked INPUT and pasted as above.

I've closed window and in put data into INPUT worksheet.
When I change to DATA worksheet, only Information field contains data.
The Date field is empty. Do I need to put a formulae in this column?
Thanks enquirer

 

[Gord Dibben]

The code does not mention 2 columns

It refers to column 2 which is column B

Your original post stated you used Column B of INPUT sheet to enter data.

Column A of DATA sheet would receive the date/time of entry of that data.

The code as written does exactly that when INPUT sheet is active and user types
into column B

Paste the code into module of INPUT sheet.

Alt + q to return to Excel.

Enter data in B1 of INPUT sheet.

See date/time in A1 of DATA sheet.

No formulas in any column of either sheet.

If you need more please email me a workbook with a description of what those
needs are.

Change phnorton to gorddibb


Gord