
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 22, 2019 09:27 AM
Hi Everyone,
I’m trying to write a formula to calculate the length of service for our volunteers based on a single select field with options Active/Inactive. I’ve gotten the formula to return a result for Active volunteers, but for the Inactive volunteers it remains empty.
Here’s the formula, how do I get a result for the Inactive volunteers?
IF({Active/Inactive}=“Active”,DATETIME_DIFF(TODAY(),{Start Date},‘days’,IF({Active/Inactive}=“Inactive”,DATETIME_DIFF({End Date},{Start Date},‘days’))))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 22, 2019 01:11 PM
Looks like you may have a parentheses in the wrong spot, try this:
IF({Active/Inactive}=“Active”, DATETIME_DIFF(TODAY(),{Start Date},‘days’), IF({Active/Inactive}=“Inactive”,DATETIME_DIFF({End Date},{Start Date},‘days’)))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Mar 22, 2019 05:14 PM
As an alternate solution, the formula can be simplified a bit using SWITCH, if only by making the parenthesis-placement issue a little easier because you’re not nesting so much:
SWITCH({Active/Inactive}, "Active", DATETIME_DIFF(TODAY(),{Start Date},'days'),
"Inactive", DATETIME_DIFF({End Date},{Start Date},‘days’))
