Formula for non blank cell

[Penny Miller]

I hope this makes sense... I'm working with a department that uses word to
keep track of when employees are doing their designated duties by system
date/time to make sure they aren't fudging the time.

I've created a document that has two different tables in it and the first
table (7 columns) can be un protected with no worries. However, the second
table (2 columns) needs to have cell A1 protected and if a user keys in any
text in B1 I need it to add the current date/time to A1 once they hit the
enter/tab key.

If B1 isnot blank then A1=CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"

Thanks

 

[macropod]

Hi Penny,

This is probably best handled by referencing the formfield's bookmark, rather than the cell. That way the calculation isn't affected
by a change in table layout. Also make sure the formfield's properties are set to 'calculate on exit'.

In A1, you could then use a formula field coded like:
{IF{REF BkMrk}= "BkMrk_Default" "{CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"}" "Result if BkMrk not Default"}
where:
.. BkMrk is the formfield's bookmark name.
.. BkMrk_Default is whatever the formfield has before being filled in. If it's the default space sequence, use " *" to trap any
sequence beginning with a space (in case the user simply hits the spacebar one or more times then tabs out of the field)
.. if there's nothing to display if the user enters nothing in the formfield, you can delete "Result if BkMrk not Default" in its
entirety.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

 

[Penny Miller]

I must have done something incorrect, when I went to fill in the formfield,
it typed in what I told it to but it didn't automattically include the
date/time in A1. Do I need to protect this portion of the document? This
is what I did:

A1 (basically, this cell looks blank unless you toggled the fied codes)
Did ctrl+f9 and inserted the code you provided (thanks by the way) and I
created a bookmark to this code called BkMrk

B1 (this cell shows that it's a form field that is shaded)
Created form field, selected Calculate on exit (the check box above it was
already checked "Fill-in enabled") and gave it the bookmark called
BkMrk_Default

Hi Penny,

This is probably best handled by referencing the formfield's bookmark,
rather than the cell. That way the calculation isn't affected by a change
in table layout. Also make sure the formfield's properties are set to
'calculate on exit'.

In A1, you could then use a formula field coded like:
{IF{REF BkMrk}= "BkMrk_Default" "{CREATEDATE \@ "yyyy-MM-dd" \@
"HH:mm:ss"}" "Result if BkMrk not Default"}
where:
. BkMrk is the formfield's bookmark name.
. BkMrk_Default is whatever the formfield has before being filled in. If
it's the default space sequence, use " *" to trap any sequence beginning
with a space (in case the user simply hits the spacebar one or more times
then tabs out of the field)
. if there's nothing to display if the user enters nothing in the
formfield, you can delete "Result if BkMrk not Default" in its entirety.

Note: The field brace pairs (ie '{ }') for the above example are created
via Ctrl-F9 - you can't simply type them or copy & paste them from this
message.

--
Cheers
macropod
[MVP - Microsoft Word]

I hope this makes sense... I'm working with a department that uses word
to keep track of when employees are doing their designated duties by
system date/time to make sure they aren't fudging the time.

I've created a document that has two different tables in it and the first
table (7 columns) can be un protected with no worries. However, the
second table (2 columns) needs to have cell A1 protected and if a user
keys in any text in B1 I need it to add the current date/time to A1 once
they hit the enter/tab key.

If B1 isnot blank then A1=CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"

Thanks

 

[macropod]

Hi Penny,

"I created a bookmark to this code called BkMrk ... Created form field ... and gave it the bookmark called BkMrk_Default"
The names 'BkMrk' and 'BkMrk_Default' I used in my reply were for illustration only, to explain what you needed to do.

Working with what you've got, 'BkMrk' (not BkMrk_Default) is the name you need to insert as the bookmark name in the formfield
properties.

Assuming your formfield starts off 'empty', try replacing "BkMrk_Default" in the field code with " *".

Yes, you do need to protect the document for forms for this to work.

--
Cheers
macropod
[MVP - Microsoft Word]

I must have done something incorrect, when I went to fill in the formfield, it typed in what I told it to but it didn't
automattically include the date/time in A1. Do I need to protect this portion of the document? This is what I did:

A1 (basically, this cell looks blank unless you toggled the fied codes)
Did ctrl+f9 and inserted the code you provided (thanks by the way) and I created a bookmark to this code called BkMrk

B1 (this cell shows that it's a form field that is shaded)
Created form field, selected Calculate on exit (the check box above it was already checked "Fill-in enabled") and gave it the
bookmark called BkMrk_Default

Hi Penny,

This is probably best handled by referencing the formfield's bookmark, rather than the cell. That way the calculation isn't
affected by a change in table layout. Also make sure the formfield's properties are set to 'calculate on exit'.

In A1, you could then use a formula field coded like:
{IF{REF BkMrk}= "BkMrk_Default" "{CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"}" "Result if BkMrk not Default"}
where:
. BkMrk is the formfield's bookmark name.
. BkMrk_Default is whatever the formfield has before being filled in. If it's the default space sequence, use " *" to trap any
sequence beginning with a space (in case the user simply hits the spacebar one or more times then tabs out of the field)
. if there's nothing to display if the user enters nothing in the formfield, you can delete "Result if BkMrk not Default" in its
entirety.

Note: The field brace pairs (ie '{ }') for the above example are created via Ctrl-F9 - you can't simply type them or copy & paste
them from this message.

--
Cheers
macropod
[MVP - Microsoft Word]

I hope this makes sense... I'm working with a department that uses word to keep track of when employees are doing their
designated duties by system date/time to make sure they aren't fudging the time.

I've created a document that has two different tables in it and the first table (7 columns) can be un protected with no worries.
However, the second table (2 columns) needs to have cell A1 protected and if a user keys in any text in B1 I need it to add the
current date/time to A1 once they hit the enter/tab key.

If B1 isnot blank then A1=CREATEDATE \@ "yyyy-MM-dd" \@ "HH:mm:ss"

Thanks