Conditional on checkbox result

Topic Labels: Formulas
1000 1
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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!


1 Reply 1
10 - Mercury
10 - Mercury

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".