Automatically Populate Dates on different days

goodwisa · May 8th, 2002, 08:47 AM

I see that there are two functions for this type of activity. I attempted to use the Now() function, but found that data entered yesterday now has today's date, instead of yesterday's date. I am going to test the Today() function and see if it works differently, but have a feeling that it will work the same. Does anybody have any insight on this?

[ This Message was edited by: goodwisa on 2002-05-08 08:48 ]

[ This Message was edited by: goodwisa on 2002-05-08 08:48 ]

Iain Lewis · May 8th, 2002, 09:06 AM

If you've left the cell as the formula it will keep updating through out the day - enter =NOW() in a cell, leave it for a minute & press F9 to re-calculate. The value for the cell will have changed because the cell is returning the current value. TODAY() will work the same( except it returns the date value, NOW() returns Date & Time ).

When you enter the formula in the cell press F9 before you hit Enter - this changes a formula to a value. Or you could use the Copy...Paste Special function to "fix" the date.

Mark W. · May 8th, 2002, 09:43 AM

Or... simply press Control+;

goodwisa · May 9th, 2002, 06:02 AM

I wanted column C to autopopulate with the date based on an entry in Column B. By putting the code in Visual Basic, it populates the cell with the value instead of the formula, so it won't change from day to day. Here is the code.

Private Sub Worksheet_Change (ByVal Target As Range)

If Target.Column <> 2 Then
Exit Sub
ElseIf Target.Value <> "" Then
Range("C" & Target.Row) = Now()
ElseIf Target.Value = "" Then
Range("C" & Target.Row) = ""
End If

End Sub

[ This Message was edited by: goodwisa on 2002-05-09 06:03 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 8th, 2002, 08:47 AM	#1
goodwisa New Member Join Date: Mar 2002 Posts: 6	I see that there are two functions for this type of activity. I attempted to use the Now() function, but found that data entered yesterday now has today's date, instead of yesterday's date. I am going to test the Today() function and see if it works differently, but have a feeling that it will work the same. Does anybody have any insight on this? [ This Message was edited by: goodwisa on 2002-05-08 08:48 ] [ This Message was edited by: goodwisa on 2002-05-08 08:48 ]

May 8th, 2002, 09:06 AM	#2
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	If you've left the cell as the formula it will keep updating through out the day - enter =NOW() in a cell, leave it for a minute & press F9 to re-calculate. The value for the cell will have changed because the cell is returning the current value. TODAY() will work the same( except it returns the date value, NOW() returns Date & Time ). When you enter the formula in the cell press F9 before you hit Enter - this changes a formula to a value. Or you could use the Copy...Paste Special function to "fix" the date.

May 8th, 2002, 09:43 AM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Or... simply press Control+;

May 9th, 2002, 06:02 AM	#4
goodwisa New Member Join Date: Mar 2002 Posts: 6	I wanted column C to autopopulate with the date based on an entry in Column B. By putting the code in Visual Basic, it populates the cell with the value instead of the formula, so it won't change from day to day. Here is the code. Private Sub Worksheet_Change (ByVal Target As Range) If Target.Column <> 2 Then Exit Sub ElseIf Target.Value <> "" Then Range("C" & Target.Row) = Now() ElseIf Target.Value = "" Then Range("C" & Target.Row) = "" End If End Sub [ This Message was edited by: goodwisa on 2002-05-09 06:03 ]