Selecting and keeping todays date

  • Thread starter Thread starter houghi
  • Start date Start date
H

houghi

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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy said:
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
Click to expand...

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


houghi
 
Sandy said:
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
Click to expand...

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
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Sandy said:
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.
Click to expand...
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
 
You're Very welcome. Thank you for the feedback.

--

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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
Back
Top