How do I make a formula date field called "Completion Date" that captures the date when a multiple choice field called "Status" is set to a particular value like "Complete"?

Knowledge Base

Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.

The best way to do this is to flip the problem around on its head.

1) Instead of making the date field a formula field, make it a plain date field.

2) Then remove the choice of "Complete" from your multiple choice "Status" field.

3) Create a formula text field called "Computed Status".

4) Put the following formula into the formula property of the "Computed Status" field.

if(isnull([Completion Date]), [Status], "Complete")

5) Now in all your views and display record forms replace the "Status" field with the "Computed Status" field.

6) On custom forms you can use alternate label text to relabel the "Computed Status" field back to "Status".

Your users will now mark a record as "Complete" by simply entering a date in the "Completion Date" field. A blank date in the "Completion Date" field means the record's status is not complete.

Tags:

36441 reads

Comments

Instead of creating a formula

Submitted by Anonymous (not verified) on Mon, 02/14/2011 - 11:58.

Instead of creating a formula text or formula date field, Its better to create a rule and set the date field to today() for the add or edit forms where the user can select the "Status" multiple choice.

Post new comment

Your name: *

E-mail: *

The content of this field is kept private and will not be shown publicly.

Homepage:

Subject:

Comment: *

Input format

Filtered HTML

Web page addresses and e-mail addresses turn into links automatically.
Allowed HTML tags: <a> <p> <hr> <br> <br /> <ul> <ol> <li> <dl> <dt> <dd> <table> <tr> <td> <em> <b> <u> <i> <strong> <font> <del> <ins> <sub> <sup> <quote> <blockquote> <pre> <address> <code> <cite> <embed> <object> <param> <strike> <caption> <img> <h1> <h2> <h3> <h4>
You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <c>, <cpp>, <drupal5>, <drupal6>, <java>, <javascript>, <php>, <python>, <ruby>, <xml>. The supported tag styles are: <foo>, [foo].
Images can be added to this post.

Plain Text

Web page addresses and e-mail addresses turn into links automatically.
Allowed HTML tags: <a> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd>

More information about formatting options

Word verification: * Type the characters you see in this picture.

(verify using audio)

Type the characters you see in the picture above; if you can't read them, submit the form and a new image will be generated. Not case sensitive.

Knowledge Base