Overdue is not working when today date is greater than Overdue

I could use some help with my formula. The "Overdue" is not working when TODAY is greater than Overdue Date. Please see formula below.

=IF(NOT(ISBLANK([Complete / Decision Date]@row )), "Completed", IF(ISBLANK([Start Date]@row ), "Not Started", IF(ISBLANK([Due Date]@row ), IF(TODAY() < [Start Date]@row , "Not Started", "In Progress"), IF(TODAY() > [Due Date]@row , "Overdue", IF(AND(TODAY() >= [Start Date]@row, TODAY() < =[Due Date]@row), "In Progress", "Not Started")))))

Thanks,

Julie

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Is the [Due Date] column set as a date type column? How exactly is it being populated?

Jgorsich

=IF(NOT(ISBLANK([Complete / Decision Date]@row  )), "Completed", 
…..IF(ISBLANK([Start Date]@row  ), "Not Started", 
……….IF(ISBLANK([Due Date]@row  ), 
………………IF(TODAY() < [Start Date]@row  , "Not Started", "In Progress"), 
…………….IF(TODAY() > [Due Date]@row  , "Overdue", 
………………………IF(AND(TODAY() >= [Start Date]@row , TODAY() < =[Due Date]@row ), "In Progress", "Not Started")
………………)
………)
….)
)

Looking at your formula, you first (A) check your complete date; if it isn't blank you call it "completed", else move on.

Then (B) you check for start date; if it is blank you call it "not started", else move on

Then (C ) you check for a due date and you logic splits:

(C1) If you DON'T have a due date you check to see if today is BEFORE the start date - if it is you call it "not started", otherwise if today is ON OR AFTER your start date you call it "In progress".

(C2) If you DO have a due date you then check to see if today is AFTER your due date, if it is you call it "overdue". If you DO have a due date and today is ON OR BEFORE your due date then you check to see if BOTH OF THE FOLLOWING ARE TRUE:

Today is the same as or after your start date
today is the same as or before your due date

If both are true you call it "in progress" else "not started".

You can only get a display of "Overdue" if all of the following is true:

complete date is EMPTY
start date has a value AND it is a date AND it is in a date column
due date has a value AND it is a date AND it is in a date column
Today() has to be greater than the value in due date

You can check if dates are dates and are in date columns by temporarily making a new date column and trying to add 1 to the values you are testing - if they are correctly formatted they'll show the next day.

Check all of the above - I'm betting your due date column might have non-date information in it.

Julie Angel

Hi Paul, my Due Date column is set as column type "Date." And I have Restrict to dates only selected.

Paul Newcome

@Julie Angel Is it manually populated or with a formula? If formula, what is the formula?

Julie Angel

Hi , I'm using the "date" function from the Column Type Drop Down list, no formula.

Paul Newcome

If it is a dropdown type column, then it is populating text strings. You'll need to make the column type a date type column.

If I am misunderstanding, are you able to provide some screenshots?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.