DATE STATIC

[Shaggy]

I need the date in "B5" to be Static according to the date that the
data was entered into "A5" (As long as the Data is greater than 1)

EG: "A5" = 19000 (Which i type in on 21 Aug 2006) so "B5" = 21 Aug 2006
"A6" = 20001 (Which i type in on 23 Aug 2006) so "B6' = 23 Aug 2006
but "A5" should stll be dated as 21 Aug 2006

This is the formula I entered into "B5", "B6" etc yesterday
=IF(A5 > 1,NOW(),"")
=IF(A6 > 1,NOW(),"")
But this updates the date to todays date and doesnt stay static for the day
that the data was into the relevant "A" cell.

Thanks for any help in advance
Shaggy

 

[Niek Otten]

http://www.mcgimpsey.com/excel/timestamp.html

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| I need the date in "B5" to be Static according to the date that the
| data was entered into "A5" (As long as the Data is greater than 1)
|
| EG: "A5" = 19000 (Which i type in on 21 Aug 2006) so "B5" = 21 Aug 2006
| "A6" = 20001 (Which i type in on 23 Aug 2006) so "B6' = 23 Aug 2006
| but "A5" should stll be dated as 21 Aug 2006
|
| This is the formula I entered into "B5", "B6" etc yesterday
| =IF(A5 > 1,NOW(),"")
| =IF(A6 > 1,NOW(),"")
| But this updates the date to todays date and doesnt stay static for the day
| that the data was into the relevant "A" cell.
|
| Thanks for any help in advance
| Shaggy

 

[Gary''s Student]

Enter this small macro in worksheet code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A5")) Is Nothing Then Exit Sub
Range("B5").Value = Now()
Range("B5").NumberFormat = "dd mmmm yyyy"
End Sub

REMEMBER: Worksheet code

 

[Shaggy]

Thanks for your response,
I am confussed, I am the first to admit that I am not the greatest at
Excel 2003, But if you wouldnt mind explaining in a bit more depth,

Regards
Shaggy

 

[Gord Dibben]

Shaggy

See my answer to your other posting....you know, the thread you already had
going.


Gord Dibben MS Excel MVP

 

[Gary''s Student]

The small program I posted is a macro. The program will run whenever cell A5
changes. The little program will put today's date in B5 (Not a formula).

All you need to do is to copy the program and paste it in the correct place.
If you are not familiar with macros, see:

http://www.mvps.org/dmcritchie/excel/getstarted.htm
or
http://www.cpearson.com/excel/codemods.htm