Formula based on other field

[Mike Guilbault]

I’m trying to write a formula that will automatically fill in today’s date when a status field has been set to “Done”.

I tried this: IF(STATUS=”Done”;TODAY();”No”) but it alwoys inputs today’s date no matter what the Status field is.

Any suggestions? TIY

[Brendan]

Hi Mike,

The IF() function’s comparison clause works only with numeric data.

If you want to do a comparison with textual data, then use the IFEQUAL() function.

For example:

IFEQUAL(Status; "Done"; DATE(TODAY(); "yyyy-MM-dd"); "No")

Also, the reason for the DATE() function in there is to convert TODAY() into a textural representation of the date. That’s because you’re returning a date if the condition is true and a text value if false. A calculation field can only return a single type of data. The value in quotes is the human readable format pattern for the date.

In the online manual for the Calculations topic, there’s a link to a webpage that shows the date format patterns you can use:

http://www.unicode.org/reports/tr35/tr35-31/tr35-dates.html#Date_Format_Patterns

Thanks,

Brendan

• This reply was modified 1 year, 7 months ago by Brendan.

[Author]

Posts