Skip to main content

Completed Formula

  • June 24, 2019
  • 1 reply
  • 13 views
H

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?

1 reply

W
Forum|alt.badge.img+5
  • W_Vann_Hall
  • Inspiring
  • June 25, 2019

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…

Terms & ConditionsAccessibility statement