Date Formula

[JAMES.GOFF25]

Hi

I am looking for a formula that automatically adds the date of entry in to another cell.

Ie everytime I add a different fruit to column A today's date will appear in the date column

<TABLE style="WIDTH: 198pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=264 x:str><COLGROUP><COL style="WIDTH: 149pt; mso-width-source: userset; mso-width-alt: 7277" width=199><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 149pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=199>Fruit</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl23 width=65>Date</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" height=17>Apple</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl22></TD></TR></TBODY></TABLE>

Thanks

 

[p45cal]

a formula will not do this, only a macro (vba).

 

[Yahya]

try this

Excel Workbook
	A	B	C
1	Fruit		Date
2	appl		2011/08/04
Sheet3

Excel 2007

Cell Formulas
Range	Formula
C2	=IF(A2="","",TEXT(TODAY(),"yyyy/mm/dd"))

 

[Yahya]

the date in my formula will change every day


by code will be better
right click on the sheets name --> View code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
ActiveCell.Offset(-1, 2).Value = Format(Date, "yyyy/mm/dd")
End If
End Sub

 

[JAMES.GOFF25]

Thanks thats solved it

 

[VoG]

the date in my formula will change every day


by code will be better
right click on the sheets name --> View code

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
ActiveCell.Offset(-1, 2).Value = Format(Date, "yyyy/mm/dd")
End If
End Sub

Hi. That will only work as intended if the user's setting is to move one cell down after pressing Enter. This would be better

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
Target.Offset(, 1).Value = Date
End If
End Sub

 

[Yahya]

Hi. That will only work as intended if the user's setting is to move one cell down after pressing Enter. This would be better

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
Target.Offset(, 1).Value = Date
End If
End Sub

yes peter
use target in this case better than activecell

thank you

 

[p45cal]

Hi. That will only work as intended if the user's setting is to move one cell down after pressing Enter. This would be better

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Columns("A")) Is Nothing Then
Target.Offset(, 1).Value = Date
End If
End Sub

By using Target.Offset(, 1).Value = Date
this code is in danger of populating other columns apart from column B; just try deleting by selecting a block of cells including column A but extending to say column D - you get a bunch of dates in columns B to E.
Try:

Private Sub Worksheet_Change(ByVal Target As Range)
Set myRng = Intersect(Target, Columns("A"))
If Not myRng Is Nothing Then
    myRng.Offset(, 1).Value = Date
End If
End Sub

This still suffers from putting dates in column B if you delete already blank cells (or cells with data in, for that matter) in column A.

 

[VoG]

Good point