Help

Re: Using approximate dates (adding "ca." or name of century) to date field?

Solved
Jump to Solution
1178 1
cancel
Showing results for 
Search instead for 
Did you mean: 
MikeEaster
5 - Automation Enthusiast
5 - Automation Enthusiast

Hello! I’m looking to link my bases for an art history research project, and one of the ones that would be helpful is to make the life dates of the artists actual dates, instead of having them as a “Single Line of Text” category like I do now. The problem is I can’t figure out if it’s possible to include text like “ca.” (or “around”) to date fields. Do you have any suggestions? I suppose if someone lived in “the early 19th century,” I could put 1800–1850 as specific dates. The other issue is I just want years, not days and months.

If I can figure out how to do all of this, I could make an interactive timeline showing the overlaps of artists’ lifetimes! That would be fantastic.

1 Solution

Accepted Solutions

@MikeEaster There are several reasons why that formula isn’t creating the output that you want.

First, you need to wrap curly braces around your field reference because it contains a forward slash. CA/FL should be {CA/FL}.

Second, the IF() function only accepts three arguments:

  • The condition that you want to test
  • The output if that condition is true
  • (optional) the output if that condition is false

Your structure uses four arguments, with the apparent assumption that the first two form the condition to test. To compare the field value against a static value, the first function should be something like this (truncated)

IF({CA/FL} = 0, "ca.", ...

Third, I can’t quite tell from your description if your single select field actually contains “0” and “1” to represent “ca.” and “fl.” respectively, or if you’re using the literal text of those options and assuming that a single select field returns a number based on the index of the selected item (which is not the case). Just to clarify, a single select field returns the literal text of the item. If your options are actually “0” and “1”, then comparing those strings against 0 and 1 (numbers) will technically work because even though the single select field returns a string, Airtable auto-converts your 1 into “1” (number to string) for an even comparison. If the field is empty, though, comparing that emptiness against 0 will actually match because an empty field is treated as false, and so is 0, which will then add the “ca.” prefix when you don’t actually want it.

However, if you have the literal text “ca.” and “fl.” as your single select options, that’s a safer option for formula comparisons, and your formula should look like this (again truncated):

IF({CA/FL} = "ca.", "ca.", ...

Finally, your second two IF() functions have spaces between “IF” and the opening parenthesis, which will lead to an error.

Those are the only actual issues with the formula design that could lead to errors or unexpected output. That said, there are some things that you could do to optimize it.

First, remember that the third argument of the IF() function is optional, and will cause the function to return nothing if the tested condition isn’t true. Therefore there’s no need to check for a blank field and return BLANK() if the other options aren’t found. With that (and the other fixes mentioned above) applied, your formula could be this:

IF({CA/FL} = "ca.", "ca.", IF({CA/FL} = "fl.", "fl."))

The next optimization that I would normally mention here is changing from nested IF() functions to the SWITCH() function because all that you’re really doing is switching the output based on what you find in the {CA/FL} field. However, it can be even simpler than that. If all that you want to do is add the selected prefix if one is chosen, then your formula could be this:

IF({CA/FL}, {CA/FL})

By only using a field reference for the first argument, Airtable treats a non-empty field as true, and an empty field as false (as long as it’s not a numeric field). That formula effectively says, “If the field isn’t empty, return its contents; otherwise return nothing.” So if your single select options are “ca.” and “fl.”, it will return the exact text of whichever one is chosen, or nothing if there’s no selection.

FWIW, I’m in the process of producing a course all about Airtable formulas, including lots of tips like those above. It’s a month or two away from completion, but if this walkthrough has helped, then keep an eye out for the announcement of the course going live.

See Solution in Thread

6 Replies 6

You would need one date field for birth, one date field for death, and one formula field for the range. The formula could be:

YEAR({Birth Date}) & " - " & YEAR({Death Date})

Welcome to the community, @MikeEaster! :grinning_face_with_big_eyes: If Kamille’s comment provided the answer you were seeking, please mark her comment as the solution to your question. This helps others who may be searching with similar questions.

Thank you so much, Kamille_Parks! I really appreciate it!

MikeEaster
5 - Automation Enthusiast
5 - Automation Enthusiast

If I may ask a follow-up question, the other issue I’m having is that sometimes the dates are approximate, and I need to add the word “ca.” to the year date, or they refer to the time in which an artist was active, in which I need to add the word “fl.”
Example: “fl. 1850–1853”

What I was thinking is having a single select column in which ca. = 0 and fl. = 1. Then I could make the following IF nested statement:

IF(CA/FL, 0, “ca.”, IF (CA/FL, 1, “fl.”, IF (CA/FL, BLANK(), BLANK())))

Then I could add it to the formula that Kamille_Parks kindly came up with for me. The problem is that it’s not valid, and I can’t for the life of me figure out why, even after looking over multiple articles on the topic in the forums. Could some Good Samaritan please edit my formula or tell me a better way to do this?

Many thanks!

M.E.

@MikeEaster There are several reasons why that formula isn’t creating the output that you want.

First, you need to wrap curly braces around your field reference because it contains a forward slash. CA/FL should be {CA/FL}.

Second, the IF() function only accepts three arguments:

  • The condition that you want to test
  • The output if that condition is true
  • (optional) the output if that condition is false

Your structure uses four arguments, with the apparent assumption that the first two form the condition to test. To compare the field value against a static value, the first function should be something like this (truncated)

IF({CA/FL} = 0, "ca.", ...

Third, I can’t quite tell from your description if your single select field actually contains “0” and “1” to represent “ca.” and “fl.” respectively, or if you’re using the literal text of those options and assuming that a single select field returns a number based on the index of the selected item (which is not the case). Just to clarify, a single select field returns the literal text of the item. If your options are actually “0” and “1”, then comparing those strings against 0 and 1 (numbers) will technically work because even though the single select field returns a string, Airtable auto-converts your 1 into “1” (number to string) for an even comparison. If the field is empty, though, comparing that emptiness against 0 will actually match because an empty field is treated as false, and so is 0, which will then add the “ca.” prefix when you don’t actually want it.

However, if you have the literal text “ca.” and “fl.” as your single select options, that’s a safer option for formula comparisons, and your formula should look like this (again truncated):

IF({CA/FL} = "ca.", "ca.", ...

Finally, your second two IF() functions have spaces between “IF” and the opening parenthesis, which will lead to an error.

Those are the only actual issues with the formula design that could lead to errors or unexpected output. That said, there are some things that you could do to optimize it.

First, remember that the third argument of the IF() function is optional, and will cause the function to return nothing if the tested condition isn’t true. Therefore there’s no need to check for a blank field and return BLANK() if the other options aren’t found. With that (and the other fixes mentioned above) applied, your formula could be this:

IF({CA/FL} = "ca.", "ca.", IF({CA/FL} = "fl.", "fl."))

The next optimization that I would normally mention here is changing from nested IF() functions to the SWITCH() function because all that you’re really doing is switching the output based on what you find in the {CA/FL} field. However, it can be even simpler than that. If all that you want to do is add the selected prefix if one is chosen, then your formula could be this:

IF({CA/FL}, {CA/FL})

By only using a field reference for the first argument, Airtable treats a non-empty field as true, and an empty field as false (as long as it’s not a numeric field). That formula effectively says, “If the field isn’t empty, return its contents; otherwise return nothing.” So if your single select options are “ca.” and “fl.”, it will return the exact text of whichever one is chosen, or nothing if there’s no selection.

FWIW, I’m in the process of producing a course all about Airtable formulas, including lots of tips like those above. It’s a month or two away from completion, but if this walkthrough has helped, then keep an eye out for the announcement of the course going live.

Dear Justin,
Thank you SO much for this wonderful explanation, it is incredibly helpful. I will try it out immediately and will absolutely keep an eye out for your course. I can’t wait to take it!

Many thanks,

M.E.