I have a file where people fill out the date of today. If I use TODAY(),
I would get the wrong dates from the dates filled out previously.

My idea would be that the day of today is filled out when something si
enterd in B1. Obviously the day may not change then next day or days
ofter that.

The reason is that people WILL mistype the data.

Is this possible and if so, how? I am working with Excel 2003.

houghi
--
We all came out to Montreux Frank Zappa and the Mothers
On the Lake Geneva shoreline Were at the best place around
To make records with a mobile But some stupid with a flare gun
We didn't have much time Burned the place to the ground

Use some VBA code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

Range("C2").Value = Int(Now)
'Change C2 to the cell reference you want
End Sub

format thbe date cell as you want the date to show

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"houghi" wrote in message
...
I have a file where people fill out the date of today. If I use TODAY(),
I would get the wrong dates from the dates filled out previously.

My idea would be that the day of today is filled out when something si
enterd in B1. Obviously the day may not change then next day or days
ofter that.

The reason is that people WILL mistype the data.

Is this possible and if so, how? I am working with Excel 2003.

houghi
--
We all came out to Montreux Frank Zappa and the Mothers
On the Lake Geneva shoreline Were at the best place around
To make records with a mobile But some stupid with a flare gun
We didn't have much time Burned the place to the ground

Sorry I forgot to say to put the code in the sheet module right-click on the
sheet tab and select View Code and select the sheet name.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Sandy Mann" wrote in message
...
Use some VBA code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

Range("C2").Value = Int(Now)
'Change C2 to the cell reference you want
End Sub

format thbe date cell as you want the date to show

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"houghi" wrote in message
...
I have a file where people fill out the date of today. If I use TODAY(),
I would get the wrong dates from the dates filled out previously.

My idea would be that the day of today is filled out when something si
enterd in B1. Obviously the day may not change then next day or days
ofter that.

The reason is that people WILL mistype the data.

Is this possible and if so, how? I am working with Excel 2003.

houghi
--
We all came out to Montreux Frank Zappa and the Mothers
On the Lake Geneva shoreline Were at the best place around
To make records with a mobile But some stupid with a flare gun
We didn't have much time Burned the place to the ground

Sandy Mann wrote:
Use some VBA code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

Range("C2").Value = Int(Now)
'Change C2 to the cell reference you want
End Sub

format thbe date cell as you want the date to show

Thanks, I will try it out tomorow when I am in fomt of the PC with Excel
again.


houghi
--
We all came out to Montreux Frank Zappa and the Mothers
On the Lake Geneva shoreline Were at the best place around
To make records with a mobile But some stupid with a flare gun
We didn't have much time Burned the place to the ground

Sandy Mann wrote:
Use some VBA code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

Range("C2").Value = Int(Now)
'Change C2 to the cell reference you want
End Sub

format thbe date cell as you want the date to show

OK, perhaps I was not clear with my explanation. I have changed B1 to
B2, so the dates are next to each other.
However I would need this for the whole column B for the whole column C.
So not only would I like the date in C2 the moment I fill something out
in B2, but for all and everything in B and C, except B1 and C1 where the
headers will be.

houghi
--
Let's not be too tough on our own ignorance. It's the thing that makes
America great. If America weren't incomparably ignorant, how could we
have tolerated the last eight years? -- Frank Zappa, in 1988

Change the Worksheet_Change Macro to:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub

Cells(Target.Row, 3).Value = Int(Now)

End Sub

And it should do what you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"houghi" wrote in message
...
Sandy Mann wrote:
Use some VBA code?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B1")) Is Nothing Then Exit Sub

Range("C2").Value = Int(Now)
'Change C2 to the cell reference you want
End Sub

format thbe date cell as you want the date to show

OK, perhaps I was not clear with my explanation. I have changed B1 to
B2, so the dates are next to each other.
However I would need this for the whole column B for the whole column C.
So not only would I like the date in C2 the moment I fill something out
in B2, but for all and everything in B and C, except B1 and C1 where the
headers will be.

houghi
--
Let's not be too tough on our own ignorance. It's the thing that makes
America great. If America weren't incomparably ignorant, how could we
have tolerated the last eight years? -- Frank Zappa, in 1988

Sandy Mann wrote:
Change the Worksheet_Change Macro to:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub

Cells(Target.Row, 3).Value = Int(Now)

End Sub

And it should do what you want.

Works like a charm. Thanks a lot. This will save me and my cow orkers a
lot of time filtering out errors in dates enterd.

houghi
--
Let's not be too tough on our own ignorance. It's the thing that makes
America great. If America weren't incomparably ignorant, how could we
have tolerated the last eight years? -- Frank Zappa, in 1988

You're Very welcome. Thank you for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"houghi" wrote in message
...
Sandy Mann wrote:
Change the Worksheet_Change Macro to:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("B:B")) Is Nothing Then Exit Sub
If Target.Row = 1 Then Exit Sub

Cells(Target.Row, 3).Value = Int(Now)

End Sub

And it should do what you want.

Works like a charm. Thanks a lot. This will save me and my cow orkers a
lot of time filtering out errors in dates enterd.

houghi
--
Let's not be too tough on our own ignorance. It's the thing that makes
America great. If America weren't incomparably ignorant, how could we
have tolerated the last eight years? -- Frank Zappa, in 1988

How do you ork a cow?

Would it hurt me or the cow?


Gord Dibben MS Excel MVP

On Fri, 21 Mar 2008 15:17:15 +0100, houghi wrote:

This will save me and my cow orkers a