Greetings,
What I have . . .
Member Table for Fire Department Operations Airtable Base
Type of Field Field Name Data in Field
DateField: Joined Date Member Joined Department
Formula: Current YOS Calculate YOS from Join Date to Today
Date Field Date Left Dept. Date Member Left Department
Formula NLM YOS Calculate YOS from Date Joined to Date Left
Date Field Date of Death Death Date While Still Member
Formula: Death YOS Calculate YOS from Date Joined to Death Date
What I need is a formula to calculate:
If
member is alive and still a member
Then
calculate Current YOS from Date Joined to Today in Current YOS Field
Else If
member left department
Then
calculate NLM YOS from Date Joined to Date Left in NLM YOS Field
Else If
member died while still a member
Then
calculate Death YOS from Date Joined to Date of Death in Death YOS Field
End If
I have worked out how to write each individual formula. I have no idea how to put all three together. And I assume I will need a formula in all three fields: Current YOS, NLM YOS and Death YOS. Correct? If so, the same formula in all three fields or variations? Also is there a way to suppress the error NaN in the fields that have no date?
The three calculated fields are mutually exclusive. Only one of the three can have a YOS in it.
I hoper this is clear enough. Thanks in advance for any and all help.
Best, Scott
Scott, the description is quite condensed so not sure if I got what you are looking for but from the formula you listed it seems like this… Makes sense?
IF(
AND(Person_field = “alive”, Status_field = “member”),
DATETIME_DIFF(TODAY(), {Date Joined}, “years”),
IF(
AND(Person_field = “alive”, Status_field = “left”),
DATETIME_DIFF({Date Left}, {Date Joined}, “years”),
IF(
AND(Person_field = “died”, Status_field = “member”),
DATETIME_DIFF({Date of Death}, {Date Joined}, “years”),
BLANK()
)
)
)
There may be errors in the formula so may need to check syntax.
Dear itoldusoandso, thanks for the reply. This looks like what I am after. I am not in a position right now to test it. I will get back to it in a day or two.and let you know how it goes. Many thanks. Scott
Hi, decided to try once tonight. This is how far I have gotten. I do not know what is wrong, but I get a formula error. The field names are correct. I tjionk I understand the use of AND. I also think I understand the logic of each If statement. But there is a syntax error somewhere. I appreciate any help on this. Thanks. Scott
IF(
AND(
{Date Left Dept.}=BLANK(),
{Date of Death}=BLANK()
),
DATETIME_DIFF(TODAY(),{Joined}, “years”),
IF(
{Current Status}=“NLM”,
DATETIME_DIFF({Date Left Dept.},{Joined}, “years”),
IF(
{Current Status}=“Deceased”,
DATETIME_DIFF({Date of Death},{Joined}, “years”),
BLANK()
)
)
)
Are you using straight quotes or curly quotes? Airtable only supports straight quotes.
Hello all, The quotes were the answer. I finally figured out the logic and triple checked my typing and now it works fine. On a Mac you need to edit the quotes in the Airtable formula edit box to get the right quotes. I usually write the code and edit in a text editor and then copy it in as I have more room and larger text. But the quotes still need to be adjusted. I also figured out that I needed one third of the formula in each field. Duh! Thank you both for your help. I learned a lot from this little problem. Now I have the LOS calculations I need. THANKS! It’s great to have access to so much knowledge and experience. Scott
1 Like
@brastedhouse
Great to hear! 
Also, when posting formulas here in the forums, people can use the “Preformatted Text” formatting option to prevent straight quotes from being changed into curly quotes. (Although if they’re already curly quotes, it doesn’t change them back into straight quotes.)
Here’s an example of what it would look like to use the “preformatted text” formatting option in these forums:
IF(
AND(
{Date Left Dept.}=BLANK(),
{Date of Death}=BLANK()
),
DATETIME_DIFF(TODAY(),{Joined}, "years"),
IF(
{Current Status}="NLM",
DATETIME_DIFF({Date Left Dept.},{Joined}, "years"),
IF(
{Current Status}="Deceased",
DATETIME_DIFF({Date of Death},{Joined}, "years"),
BLANK()
)
)
)
@brastedhouse
Also, one other thing to mention is that when I pasted your formula into the Nova text editor for Mac, it automatically straightened all the quotes upon pasting.
I really like the Nova app, although I know that there are dozens of different text editors to choose from.
Again, thanks. I’ll try it next time. Best, Scott
1 Like