if a cell contains text, then display date and time

[cassandra112]

HI,

This is probably simple but i'm having a tough time....
I am creating a note sheet for clients....if a client posts a note, I want
the cell next to it to display the date and time it was posted. I know I need
an if formula with istext and now in it but I can't seem to make it
work....Help!

Thanks
Cassandra 112

[Dave O]

Hi, Cassandra-
This formula worked:
=IF(AND(C5<>"",ISTEXT(C5)),NOW(),"")
It checks to see that the cell in question is both text and non-blank
and returns the time, or a blank if that condition is not true.

HOWEVER... the problem I see is the NOW() function evaluates
differently as time passes. If your client enters text at 8 am, that
time will appear in this formula. The next time the client performs
any calculation, the NOW() function will update to the current system
time, and you'll lose data integrity.

The only solution I can think of is to write some VBA code that will
copy that cell and paste it as a value- easily enough done, but it
depends on your familiarity with this. How comfortable are you with
that idea?

[cassandra112]

Dave O

Thanks so much for trying to help!
Unfortunately I'm not at all familiar with VBA....any other suggestions?

THanks again
Cassandra

"Dave O" wrote:

> Hi, Cassandra-
> This formula worked:
> =IF(AND(C5<>"",ISTEXT(C5)),NOW(),"")
> It checks to see that the cell in question is both text and non-blank
> and returns the time, or a blank if that condition is not true.
>
> HOWEVER... the problem I see is the NOW() function evaluates
> differently as time passes. If your client enters text at 8 am, that
> time will appear in this formula. The next time the client performs
> any calculation, the NOW() function will update to the current system
> time, and you'll lose data integrity.
>
> The only solution I can think of is to write some VBA code that will
> copy that cell and paste it as a value- easily enough done, but it
> depends on your familiarity with this. How comfortable are you with
> that idea?
>
>

[Dave O]

No other ideas that are automated. To set it up with VBA you'd need to
determine the range of cells where comments are expected, and trigger
an event when one of those cells is changed. When that event triggers,
the code would insert the current date and time in the cell immediately
adjacent.

What is the tab name where the user is entering comments, and what is
the range of cells where this is likely to happen? It can be all of
column B, for instance, if that's the correct answer, just need to have
a range.

I have an answer for you, but it may take a while: I"m off to lunch and
then I have a series of meetings.

[cassandra112]

Dave O

You are an angel....thanks so much for your time but I think I have it
working using a circular reference....

I checked the iteration box under the tools/options/calculations menu and
set up this circular reference

=IF(B1="","",IF(A1="",NOW(),A1))

Now whenever I enter a comment in B, I get a static time/date stamp in A.
Haven't had any problems so far.

Thanks again
Cassandra

"Dave O" wrote:

> No other ideas that are automated. To set it up with VBA you'd need to
> determine the range of cells where comments are expected, and trigger
> an event when one of those cells is changed. When that event triggers,
> the code would insert the current date and time in the cell immediately
> adjacent.
>
> What is the tab name where the user is entering comments, and what is
> the range of cells where this is likely to happen? It can be all of
> column B, for instance, if that's the correct answer, just need to have
> a range.
>
> I have an answer for you, but it may take a while: I"m off to lunch and
> then I have a series of meetings.
>
>

[cassandra112]

Dave O

Maybe you could help me with one other question?
I inserted a word document using insert object into a worksheet. Now,
though, whenever I try to print it there is a border around the document. Do
you know how to get rid of this? Really appreciate any suggestions...

Thanks again
Cassandra

"cassandra112" wrote:

> Dave O
>
> You are an angel....thanks so much for your time but I think I have it
> working using a circular reference....
>
> I checked the iteration box under the tools/options/calculations menu and
> set up this circular reference
>
> =IF(B1="","",IF(A1="",NOW(),A1))
>
> Now whenever I enter a comment in B, I get a static time/date stamp in A.
> Haven't had any problems so far.
>
> Thanks again
> Cassandra
>
> "Dave O" wrote:
>
> > No other ideas that are automated. To set it up with VBA you'd need to
> > determine the range of cells where comments are expected, and trigger
> > an event when one of those cells is changed. When that event triggers,
> > the code would insert the current date and time in the cell immediately
> > adjacent.
> >
> > What is the tab name where the user is entering comments, and what is
> > the range of cells where this is likely to happen? It can be all of
> > column B, for instance, if that's the correct answer, just need to have
> > a range.
> >
> > I have an answer for you, but it may take a while: I"m off to lunch and
> > then I have a series of meetings.
> >
> >