NOW() 
Author Message
 NOW()

Is there a built in way to have a date remain fixed once entered instead of
updating each day, short of entering the date manually?  is there a macro that
could do this automatically when the date NOW function is excercised?

For  example, a cell value is blank until a condition is met, then the NOW
function records the date and it is converted (paste special?)

Thanks

--

Harvey Waxman
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.*-*-*.com/



Thu, 24 Nov 2005 21:13:44 GMT  
 NOW()
Harvey Waxman wrote :

Quote:
> Is there a built in way to have a date remain fixed once entered instead
> of updating each day, short of entering the date manually?  is there a
> macro that  could do this automatically when the date NOW function is
> excercised?

> For  example, a cell value is blank until a condition is met, then the
> NOW function records the date and it is converted (paste special?)

You can, of course, enter the date trough a macro. A very simple line like:
    ActiveCell = Now
would be enough. The trick is how to launch it? It depends upon the
condition in which it is supposed to be modified

One way to do this would be to use the macro below on the concerned
worksheet's Code sheet (Control-click the tab to reach it):

Private Sub Worksheet_Calculate()

If Range("A1") = "1" And Range("A2") <> "" Then
    Range("B2") = Now
End If

End Sub

In this case, I suppose it's in cell B2 you'd like to have the "fixed" date.
So as long as the condition in cell A1 hasn't been met nothing would happen.
When the condition it met (here when A1 = 1) the date  and time would be
added in cell B2. And then never again (unless you clear B2)...

--
Bernard Rey - Toulouse / France
MVP - Macintosh



Thu, 24 Nov 2005 23:42:11 GMT  
 NOW()

Quote:

> Harvey Waxman wrote :

> > Is there a built in way to have a date remain fixed once entered instead
> > of updating each day, short of entering the date manually?  is there a
> > macro that  could do this automatically when the date NOW function is
> > excercised?

> > For  example, a cell value is blank until a condition is met, then the
> > NOW function records the date and it is converted (paste special?)

> You can, of course, enter the date trough a macro. A very simple line like:
>     ActiveCell = Now
> would be enough. The trick is how to launch it? It depends upon the
> condition in which it is supposed to be modified

> One way to do this would be to use the macro below on the concerned
> worksheet's Code sheet (Control-click the tab to reach it):

> Private Sub Worksheet_Calculate()

> If Range("A1") = "1" And Range("A2") <> "" Then
>     Range("B2") = Now
> End If

> End Sub

> In this case, I suppose it's in cell B2 you'd like to have the "fixed" date.
> So as long as the condition in cell A1 hasn't been met nothing would happen.
> When the condition it met (here when A1 = 1) the date  and time would be
> added in cell B2. And then never again (unless you clear B2)...

I'm clueless when it comes to using the editor.  I can record macros but don't
know where to paste this code. Can't wait to try it though. It looks exactly
like what I was looking for.

  Thanks.

--

Harvey Waxman
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com



Fri, 25 Nov 2005 01:02:01 GMT  
 NOW()

Quote:
> I'm clueless when it comes to using the editor.  I can record macros but don't
> know where to paste this code. Can't wait to try it though. It looks exactly
> like what I was looking for.

Ccontrol-click the concerned sheet's tab. That'll open the linked code sheet
(and the VB Editor).

Simply paste the lines on the code sheet and close it, here you are...

--
Bernard Rey - Toulouse / France
MVP - Macintosh

Harvey Waxman wrote :

Quote:

>> Harvey Waxman wrote :

>>> Is there a built in way to have a date remain fixed once entered instead
>>> of updating each day, short of entering the date manually?  is there a
>>> macro that  could do this automatically when the date NOW function is
>>> excercised?

>>> For  example, a cell value is blank until a condition is met, then the
>>> NOW function records the date and it is converted (paste special?)

>> You can, of course, enter the date trough a macro. A very simple line like:
>>     ActiveCell = Now
>> would be enough. The trick is how to launch it? It depends upon the
>> condition in which it is supposed to be modified

>> One way to do this would be to use the macro below on the concerned
>> worksheet's Code sheet (Control-click the tab to reach it):

>> Private Sub Worksheet_Calculate()

>> If Range("A1") = "1" And Range("A2") <> "" Then
>>     Range("B2") = Now
>> End If

>> End Sub

>> In this case, I suppose it's in cell B2 you'd like to have the "fixed" date.
>> So as long as the condition in cell A1 hasn't been met nothing would happen.
>> When the condition it met (here when A1 = 1) the date  and time would be
>> added in cell B2. And then never again (unless you clear B2)...



Fri, 25 Nov 2005 01:19:05 GMT  
 NOW()
Bernard Rey wrote :

Quote:
>> I'm clueless when it comes to using the editor.  I can record macros but
>> don't
>> know where to paste this code. Can't wait to try it though. It looks exactly
>> like what I was looking for.

> Ccontrol-click the concerned sheet's tab. That'll open the linked code sheet
> (and the VB Editor).

> Simply paste the lines on the code sheet and close it, here you are...

I see I've been doing some mistyping again (tried several issues, and then
modified it on the run...) In order to work as I expected it should read:

 Private Sub Worksheet_Calculate()

  If Range("A1") = "1" And Range("A2") = "" Then
      Range("A2") = Now
  End If          

 End Sub

If just entering the value 1 in A1 should be enough to display the value,
then it should rather be a "Worksheet_Change" event macro:

 Private Sub Private Sub Worksheet_Change(ByVal Target As Excel.Range)()

  If Range("A1") = "1" And Range("A2") = "" Then
      Range("A2") = Now
  End If          

 End Sub

Hope I made it clearer (and did not sparse any mistakes around again,
leading poor Harvey to think macros are way too difficult) :-)

--
Bernard Rey - Toulouse / France
MVP - Macintosh



Fri, 25 Nov 2005 07:28:28 GMT  
 NOW()


Quote:
> Is there a built in way to have a date remain fixed once entered instead of
> updating each day, short of entering the date manually?  is there a macro that
> could do this automatically when the date NOW function is excercised?

> For  example, a cell value is blank until a condition is met, then the NOW
> function records the date and it is converted (paste special?)

> Thanks

If you just want to enter the current date into a cell type control-semi
colon.

--
Bob Greenblatt [Excel MVP]

www.bandlassoc.com



Fri, 25 Nov 2005 20:31:39 GMT  
 NOW()


Quote:
> If you just want to enter the current date into a cell type control-semi
> colon.

Same as cmd"-"   ?  

Bernard Ray helped me set up a macro that does the conditional date entry just
as I wanted so I can be even lazier than I was before.  Actually it is always
fun to see how these macros work and I may even have learned a little bit.

--

Harvey Waxman
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com



Sat, 26 Nov 2005 10:43:59 GMT  
 NOW()


Quote:


>> If you just want to enter the current date into a cell type control-semi
>> colon.

> Same as cmd"-"   ?

> Bernard Ray helped me set up a macro that does the conditional date entry just
> as I wanted so I can be even lazier than I was before.  Actually it is always
> fun to see how these macros work and I may even have learned a little bit.

Yep! both do the same thing.

--
Bob Greenblatt [Excel MVP]

www.bandlassoc.com



Sat, 26 Nov 2005 20:46:01 GMT  
 NOW()


Quote:




> >> If you just want to enter the current date into a cell type control-semi
> >> colon.

> > Same as cmd"-"   ?

> > Bernard Ray helped me set up a macro that does the conditional date entry
> > just
> > as I wanted so I can be even lazier than I was before.  Actually it is
> > always
> > fun to see how these macros work and I may even have learned a little bit.

> Yep! both do the same thing.

NOW() if I can only stop the time from insisting on showing up!  Formatting
with m/d/yy doesn't help.  Times still show

--

Harvey Waxman
Harvey Products makers of Dinghy Dogs(TM)
The Boater's Best Friend
http://www.dinghydogs.com



Sat, 26 Nov 2005 21:01:55 GMT  
 NOW()


Quote:






> > >> If you just want to enter the current date into a cell type control-semi
> > >> colon.

> > > Same as cmd"-"   ?

> > > Bernard Ray helped me set up a macro that does the conditional date entry
> > > just
> > > as I wanted so I can be even lazier than I was before.  Actually it is
> > > always
> > > fun to see how these macros work and I may even have learned a little bit.

> > Yep! both do the same thing.

> NOW() if I can only stop the time from insisting on showing up!  Formatting
> with m/d/yy doesn't help.  Times still show

Never mind. The answer is too embarrassing to share :-)

--

Harvey Waxman
Harvey Products makers of Dinghy Dogs
The Boater's Best Friend
http://www.dinghydogs.com



Sat, 26 Nov 2005 21:07:34 GMT  
 
 [ 10 post ] 

 Relevant Pages 

 
Powered by phpBB® Forum Software © phpBB Group