DATE STATIC

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

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"Shaggy" <[email protected]> wrote in message news:[email protected]...
| 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

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
--
Gary's Student


"Shaggy" wrote:

> 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

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

"Gary''s Student" wrote:

> 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
> --
> Gary's Student
>
>
> "Shaggy" wrote:
>
> > 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

Shaggy

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


Gord Dibben MS Excel MVP

On Mon, 21 Aug 2006 15:11:58 -0700, Shaggy <[email protected]>
wrote:

> 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

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
--
Gary''s Student


"Shaggy" wrote:

> 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
>
> "Gary''s Student" wrote:
>
> > 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
> > --
> > Gary's Student
> >
> >
> > "Shaggy" wrote:
> >
> > > 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