Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Date Field Formula when One date is Blank

1150 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Sarah_Zavitz
4 - Data Explorer
4 - Data Explorer

Hello,

I am trying to show the summary of years members have participated in a non-profit.
There are five fields: Name, Active (checkbox), Date Joined, Date Left, Years of Membership

The table has both active and inactive members. I am using the time diff formula, however, for the members that are still active, the Date Left category returns NaN value.

Is there a way of calculating a Years of Membership for both active and inactive members in the same column?

Thank you!

3 Replies 3

What is your current formula?

You would essentially wrap it in an IF statement to come up with 2 different results.

Sarah_Zavitz
4 - Data Explorer
4 - Data Explorer

Thank you for taking the time to respond!
Our code is: DATETIME_DIFF({Date Leaving},{Date Joined},‘years’)

Would it be possible for the IF statement be able to say blank = today’s date? That would allow an ongoing calculation and be extremely beneficial.

You can use this formula:

DATETIME_DIFF(
IF({Date Leaving}="",TODAY(),{Date Leaving}),
{Date Joined},
'years')