Automatically filling date of today (without it changing tomorrow)

I have tw columns
Column A => Date
Column B => Questions

In column A I have a formula =IF(B2<>"";TODAY();""). This means, that if I
type a letter in column B and press enter the date of today is automatically
filled in in Column A.

With the usage of Today() the date will change tomorrow, to tomorrow's date.
I don't want it to change. So what should happen is:

I type in a letter (or sentence or whatever) in column B today (which is 27
june 2006) is automatically entered. Tomorrow I want to check the question
and the date in front of it remains 27 june 2006.

Does this make sense?

Please help me......Thanx


--
** Fool on the hill **

Apply this event macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row > 1 Then
Range("A" & Target.Row) = Date
End If
End Sub

Post if you need help to install it!

Regards,
Stefi


„Jaydubs” ezt �*rta:

> I have tw columns
> Column A => Date
> Column B => Questions
>
> In column A I have a formula =IF(B2<>"";TODAY();""). This means, that if I
> type a letter in column B and press enter the date of today is automatically
> filled in in Column A.
>
> With the usage of Today() the date will change tomorrow, to tomorrow's date.
> I don't want it to change. So what should happen is:
>
> I type in a letter (or sentence or whatever) in column B today (which is 27
> june 2006) is automatically entered. Tomorrow I want to check the question
> and the date in front of it remains 27 june 2006.
>
> Does this make sense?
>
> Please help me......Thanx
>
>
> --
> ** Fool on the hill **

Hello Stefi,

Great this works.

However, I did see that if I change the date and later change the text, the
date is changed back to the original. Is this normal?? I might want to change
the text, but not the date !!

But thanks, this has helped me a lot!! Will save me a lot of work in the
future !!

-
** Fool on the hill **


"Stefi" wrote:

> Apply this event macro:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 2 And Target.Row > 1 Then
> Range("A" & Target.Row) = Date
> End If
> End Sub
>
> Post if you need help to install it!
>
> Regards,
> Stefi
>
>
> „Jaydubs” ezt �*rta:
>
> > I have tw columns
> > Column A => Date
> > Column B => Questions
> >
> > In column A I have a formula =IF(B2<>"";TODAY();""). This means, that if I
> > type a letter in column B and press enter the date of today is automatically
> > filled in in Column A.
> >
> > With the usage of Today() the date will change tomorrow, to tomorrow's date.
> > I don't want it to change. So what should happen is:
> >
> > I type in a letter (or sentence or whatever) in column B today (which is 27
> > june 2006) is automatically entered. Tomorrow I want to check the question
> > and the date in front of it remains 27 june 2006.
> >
> > Does this make sense?
> >
> > Please help me......Thanx
> >
> >
> > --
> > ** Fool on the hill **

If you want to leave unchanged dates already in column A, use this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 And Target.Row > 1 And IsEmpty(Range("A" &
Target.Row)) Then
Range("A" & Target.Row) = Date
End If
End Sub

Regards,
Stefi

„Jaydubs” ezt �*rta:

> Hello Stefi,
>
> Great this works.
>
> However, I did see that if I change the date and later change the text, the
> date is changed back to the original. Is this normal?? I might want to change
> the text, but not the date !!
>
> But thanks, this has helped me a lot!! Will save me a lot of work in the
> future !!
>
> -
> ** Fool on the hill **
>
>
> "Stefi" wrote:
>
> > Apply this event macro:
> >
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Target.Column = 2 And Target.Row > 1 Then
> > Range("A" & Target.Row) = Date
> > End If
> > End Sub
> >
> > Post if you need help to install it!
> >
> > Regards,
> > Stefi
> >
> >
> > „Jaydubs” ezt �*rta:
> >
> > > I have tw columns
> > > Column A => Date
> > > Column B => Questions
> > >
> > > In column A I have a formula =IF(B2<>"";TODAY();""). This means, that if I
> > > type a letter in column B and press enter the date of today is automatically
> > > filled in in Column A.
> > >
> > > With the usage of Today() the date will change tomorrow, to tomorrow's date.
> > > I don't want it to change. So what should happen is:
> > >
> > > I type in a letter (or sentence or whatever) in column B today (which is 27
> > > june 2006) is automatically entered. Tomorrow I want to check the question
> > > and the date in front of it remains 27 june 2006.
> > >
> > > Does this make sense?
> > >
> > > Please help me......Thanx
> > >
> > >
> > > --
> > > ** Fool on the hill **

Excellent !!

Thanx a million !
--
** Fool on the hill **


"Stefi" wrote:

> If you want to leave unchanged dates already in column A, use this:
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column = 2 And Target.Row > 1 And IsEmpty(Range("A" &
> Target.Row)) Then
> Range("A" & Target.Row) = Date
> End If
> End Sub
>
> Regards,
> Stefi
>
> „Jaydubs” ezt �*rta:
>
> > Hello Stefi,
> >
> > Great this works.
> >
> > However, I did see that if I change the date and later change the text, the
> > date is changed back to the original. Is this normal?? I might want to change
> > the text, but not the date !!
> >
> > But thanks, this has helped me a lot!! Will save me a lot of work in the
> > future !!
> >
> > -
> > ** Fool on the hill **
> >
> >
> > "Stefi" wrote:
> >
> > > Apply this event macro:
> > >
> > > Private Sub Worksheet_Change(ByVal Target As Range)
> > > If Target.Column = 2 And Target.Row > 1 Then
> > > Range("A" & Target.Row) = Date
> > > End If
> > > End Sub
> > >
> > > Post if you need help to install it!
> > >
> > > Regards,
> > > Stefi
> > >
> > >
> > > „Jaydubs” ezt �*rta:
> > >
> > > > I have tw columns
> > > > Column A => Date
> > > > Column B => Questions
> > > >
> > > > In column A I have a formula =IF(B2<>"";TODAY();""). This means, that if I
> > > > type a letter in column B and press enter the date of today is automatically
> > > > filled in in Column A.
> > > >
> > > > With the usage of Today() the date will change tomorrow, to tomorrow's date.
> > > > I don't want it to change. So what should happen is:
> > > >
> > > > I type in a letter (or sentence or whatever) in column B today (which is 27
> > > > june 2006) is automatically entered. Tomorrow I want to check the question
> > > > and the date in front of it remains 27 june 2006.
> > > >
> > > > Does this make sense?
> > > >
> > > > Please help me......Thanx
> > > >
> > > >
> > > > --
> > > > ** Fool on the hill **

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'when entering data in a cell in Col B
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 2 Then
n = Target.Row
If Excel.Range("B" & n).Value <> "" Then
Excel.Range("A" & n).Value = Format(Now, "dd mmm yyyy hh:mm")
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 code into that module.


Gord Dibben MS Excel MVP

On Tue, 27 Jun 2006 01:31:02 -0700, Jaydubs <[email protected]>
wrote:

>I have tw columns
>Column A => Date
>Column B => Questions
>
>In column A I have a formula =IF(B2<>"";TODAY();""). This means, that if I
>type a letter in column B and press enter the date of today is automatically
>filled in in Column A.
>
>With the usage of Today() the date will change tomorrow, to tomorrow's date.
>I don't want it to change. So what should happen is:
>
>I type in a letter (or sentence or whatever) in column B today (which is 27
>june 2006) is automatically entered. Tomorrow I want to check the question
>and the date in front of it remains 27 june 2006.
>
>Does this make sense?
>
>Please help me......Thanx

Works great. How do I use this for multiple cells in the same worksheet? In other words, columns A and B are taken care of, but what if I want to do the same thing to Columns F and G? I input data into column F and the "now" function should go into column G, not at the same time. I have two operators who input data at different intervals. One operator uses columns A and B and the other uses Columns F and G.