We 're a nonprofit, and we keep a database of foundations to which we plan to apply for grants. Some of them have no deadlines, while others have 1, 2, 3, or 4 deadlines per year.
We record each deadline in a separate column/field (we’re using the free version, so we can’t access the multiple dates feature), with field titles {Deadline 1}, {Deadline 2}, etc.
We now have a new field that outputs the next deadline in reader-friendly format, if there is one, based on a thorough nesting of IF() statements. Here, the statement names “IF2”, “IF3”, etc. are for keeping track of things while assembling, but the final assembly at the end just includes the conditional statements.
IF1: If Deadline 1 is blank, blank, else IF2
IF({Deadline 1}=BLANK(), BLANK(), IF2)
IF2: If Deadline 1 is after today, Deadline 1, else IF3
IF(IS_AFTER({Deadline 1}, TODAY()), DATETIME_FORMAT({Deadline 1}, 'll'), IF3)
IF3: If Deadline 2 is blank, IF9, else IF4
IF({Deadline 2}=BLANK(), IF9, IF4)
IF4: If Deadline 2 is after today, Deadline 2, else IF5
IF(IS_AFTER({Deadline 2}, TODAY()), DATETIME_FORMAT({Deadline 2}, 'll'), IF5)
IF5: If Deadline 3 is blank, IF9, else IF6
IF({Deadline 3}=BLANK(), IF9, IF6)
IF6: If Deadline 3 is after today, Deadline 3, else IF7
IF(IS_AFTER({Deadline 3}, TODAY()), DATETIME_FORMAT({Deadline 3}, 'll'), IF7)
IF7: If Deadline 4 is blank, IF9, else IF8
IF({Deadline 4}=BLANK(), IF9, IF8)
IF8: If Deadline 4 is after today, Deadline 4, else IF9
IF(IS_AFTER({Deadline 4}, TODAY()), DATETIME_FORMAT({Deadline 4}, 'll'), IF9)
IF9: If Deadline 1 is blank, blank, else Deadline 1 + 365 days
IF({Deadline 1}=BLANK(), BLANK(), DATETIME_FORMAT(DATEADD({Deadline 1}, 1, 'year'), 'll'))
Added all together:
IF({Deadline 1}=BLANK(), BLANK(),IF(IS_AFTER({Deadline 1}, TODAY()), DATETIME_FORMAT({Deadline 1}, 'll'), IF({Deadline 2}=BLANK(), IF({Deadline 1}=BLANK(), BLANK(), DATETIME_FORMAT(DATEADD({Deadline 1}, 1, 'year'), 'll')), IF(IS_AFTER({Deadline 2}, TODAY()), DATETIME_FORMAT({Deadline 2}, 'll'), IF({Deadline 3}=BLANK(), IF({Deadline 1}=BLANK(), BLANK(), DATETIME_FORMAT(DATEADD({Deadline 1}, 1, 'year'), 'll')), IF(IS_AFTER({Deadline 3}, TODAY()), DATETIME_FORMAT({Deadline 3}, 'll'),IF({Deadline 4}=BLANK(), IF({Deadline 1}=BLANK(), BLANK(), DATETIME_FORMAT(DATEADD({Deadline 1}, 1, 'year'), 'll')), IF(IS_AFTER({Deadline 4}, TODAY()), DATETIME_FORMAT({Deadline 4}, 'll'), IF({Deadline 1}=BLANK(), BLANK(), DATETIME_FORMAT(DATEADD({Deadline 1}, 1, 'year'), 'll'))))))))))
I wrote this in a text editor and copied over to the formula field. If this throws an error, the most common cause I found was the format of the apostrophes around ‘ll’.
