- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
- Report Due Date will always have a date
- Date the report is submitted may be blank
How can I make a formula based off Report Due Dates saying the status if the date in the “Report Submitted” column is blank?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- No report submitted,
TODAY()
is after due date: ‘Overdue’. - No report submitted,
TODAY()
is before due date: ‘In progress’. - Report submitted before due date,
TODAY()
is before due date: ‘Up and coming’? ‘Completed’? - Report submitted before due date,
TODAY()
is after due date: ‘Completed’? - Report submitted after due date,
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…