Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 01, 2023 02:04 PM - edited Dec 01, 2023 02:08 PM
Hi,
I am trying to populate in a cell, "Urgent" when a project is coming up on its {Due Date} within 7 days of today AND the cell that has my completed date, {Date Filed} is Empty. If {Date Filed} is Not Empty, i'd like it to populate "Completed". If {Due Date} is over 7 days away and {Date Filed} is Empty, i'd like it to populate "Pending".
Any assistance is appreciated. Thanks!
Dec 01, 2023 11:30 PM
Hi,
Use formula with DATETIME_DIFF
I'd start with
IF( {Date Filed} , 'Completed,
IF(7>=ABS(DATETIME_DIFF... ,
IF(IS_AFTER....
Dec 04, 2023 07:09 AM
Hi,
Sorry for my ignorance. Could you show how that might look more drawn out? I was easily able to get the first formula indicating "Completed", but the 2 following I'm having issues writing out and not getting an error.
Thanks!
Dec 05, 2023 08:03 AM
Hi,
https://support.airtable.com/docs/formula-field-reference#date-and-time-functions
Well, the about of brackets is quite hard to set them properly, without experience . I would recommend to use wider editor window, or notepad to find/replace, and code-like style to not mess with statements
that should work
Dec 05, 2023 12:58 PM
That is super helpful Alexey. That worked and I appreciate the feedback as well while I get started with AirTable.
I didnt think of it till just now, but would you be able to provide an edit to include something that will change the cell content to show, "Past Due", if the due date is past due and hasnt been filed? Currently it shows "Pending" when past due.
Dec 07, 2023 10:09 PM
Hi,
if I understood you well, it's quite simple - just substitute text in formula, set "Past Due" instead of "Pending".
IF({Date Filed},'Completed',
IF(7>=ABS(DATETIME_DIFF(TODAY(),{Due Date},'days')),
IF(IS_AFTER({Due Date},TODAY()),'Urgent','Past Due')
)
)