Skip to main content

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

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

Reply


Terms & Conditions