Hello! I am an absolute newbie to databases, spreadsheets, the whole nine yards. I am working on a DATETIME_DIFF formula that I want to either show a fixed duration (based on two dates) if the status in Column X is A, B, or C, and show a duration using the TODAY formula if the status in Column X is not one of A, B, or C.
Ideally, for those entries with A, B, or C in Column X (where Column X is a single-select field), the DATETIME_DIFF formula will pull the end date from Column Y, and for those entries without A, B, or C in Column X, the DATETIME_DIFF formula will pull the end date from Column Z.
So for entries with end-dates (i.e. the ones that are tagged A, B, or C in Column X), it will always show the number (e.g. 38 years from start date to end date), but for ones that are ongoing, it will update to today’s date (i.e., this year it will say 25 years from start date til today, next year it will say 26 years, etc.).
I tried this:
IF(Posture = OR(Commuted, Paroled, Deceased), DATETIME_DIFF({End date of incarceration}, {Effective date of sentence}, ‘years’), DATETIME_DIFF({Still incarcerated}, {Effective date of sentence}, ‘years’))
But when I try to save, the formula says that “Commuted, Paroled, Deceased” are not fields–which is true, they are possible selections in a field.
So A:
Any advice on how to get the IF function to be triggered by possible selections in a “Single Select” field?
Or B:
Is there a workaround which would just let me put “today” (or some equivalent function) in for some of the entries in the “End date of incarceration” column and it will continually update?
Thank you in advance for any help!
