Is there a way to format now(), or today( ) to enter when a cell is inputed.
I'm trying to record the date when I pay a bill, but everytime I open up the
worksheet the date recalculates. Here's what I have so far.
C1="Paid Amount"
D1=if(C1="","",C1=NOW())
like I said though everytime I open the worksheet, say I inputed the bill on
6/7/2006, It converts it to today like I just did it..... Very frustrated...

Thanks for your time
Marty

Marty

You can enter a static date in a cell by hitting CTRL + ;(semi-colon)

You could also use event code to enter a static date when you enter something in
a cell.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col C
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 3 Then
n = Target.Row
If Excel.Range("C" & n).Value < "" Then
Excel.Range("D" & n).Value = Format(Now, "dd mm yyyy")
End If
End If
enditall:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the above into that module.

Enter a value in C1 and D1 will return a static date.


Gord Dibben MS Excel MVP

On Tue, 23 Jan 2007 12:21:02 -0800, Marty
wrote:

Is there a way to format now(), or today( ) to enter when a cell is inputed.
I'm trying to record the date when I pay a bill, but everytime I open up the
worksheet the date recalculates. Here's what I have so far.
C1="Paid Amount"
D1=if(C1="","",C1=NOW())
like I said though everytime I open the worksheet, say I inputed the bill on
6/7/2006, It converts it to today like I just did it..... Very frustrated...

Thanks for your time
Marty