Jun 24, 2019 02:03 PM
I need to make a formula. In it, I will have a report due date, then the date the report was submitted. I want to make a report progress column that states if the report is ‘overdue’, ‘in progress’, or ‘up and coming’. However, here’s my problem:
How can I make a formula based off Report Due Dates saying the status if the date in the “Report Submitted” column is blank?
Jun 24, 2019 07:34 PM
I assume a report with a blank submission date will be ‘In progress’, unless the due date is before today, in which case it’s over due? And one where the submission date is before the due date is ‘up and coming’?
Try
IF(
{Report Due Date},
IF(
NOT(
{Report Submitted}
),
IF(
IS_AFTER(
TODAY(),
{Report Due Date}
),
'Overdue',
'In Progress'
),
'Up and Coming'
)
)
(The first IF()
statement is simply to keep the field from displaying #ERROR!
after the record is created but before {Report Due Date}
has been entered.)
Edit: Wait; that’s possibly wrong. I think you need another branch or two.
TODAY()
is after due date: ‘Overdue’.TODAY()
is before due date: ‘In progress’.TODAY()
is before due date: ‘Up and coming’? ‘Completed’?TODAY()
is after due date: ‘Completed’?TODAY()
is either before or after due date: ‘Completed’? ‘Overdue’?You’ll probably want to adjust the formula above to take the possible ‘Completed’ status into account…