Help

Welcome to the Airtable Community! If you're new here, check out our Getting Started area to get the most out of your community experience.

Conditional on checkbox result

Topic Labels: Formulas
572 1
cancel
Showing results for 
Search instead for 
Did you mean: 

This one is simple, but I can’t quite make it work.

Date field called: “event date” which is manually filled in
Date field called “days until due” which is a calculated field
Date field called “files due date” which is a calculated field
Checkbox field called “review done”

I want to calculate the “files due date” based on “event date” + 21 calendar days and the checkbox “review done” being blank.

I have the following:
Days Until Due = DATEADD({Event Date},21, ‘days’)
Files Due Date: = DATETIME_DIFF({Days Until Due}, TODAY(), ‘days’)

I want to combine the two above into a single formula which returns the number of days if the checkbox is blank, but returns nothing if the check box IS checked. Important: if the “Files Due Date” is today’s day, the result needs to be “0” not blank.

Thanks in advance!

Jeremy

1 Reply 1

Not sure how you’d like to combine/display the date fields, so skipping that bit. But otherwise I think you’re just looking for the IF statement and maybe a demonstration of formulas within formulas? Something like:

IF( {Review Done}, "", {Days Until Due} )

You could also replace those {field references} with the code you’ve written if cutting down on the number of fields used is a high priority. For example:

IF( {Review Done}, "", DATEADD({Event Date},21, ‘days’) )

To combine those dates, make sure you format them properly (DATETIME_FORMAT) if they come out looking like gibberish. So your whole dateadd function would be inside of the datetime_format function. Other than that you’re probably just string concatenating. So "this date string" & " this other date" becomes "this date string this other date".