Due Date with Conditions

lesmickin

I am trying to figure out how to calculate days an action is overdue.

I have calculated the due date, but can't seem to figure out how to determine if action is over due

My fields are as follows: Ackn Letter Due Date, Ackn Letter date, Days Overdue, Date Assigned

My Ackn Letter due date is set up as a formula that captures 7 days after Date Assigned. If the Ackn Letter date is blank I need it to show how many days the task is overdue - but only capturing if there is a date assigned attached to the task (otherwise it trys to grab dates, etc for my subtasks)

Date Assigned Ackn letter Due Date Ackn Letter Date Days Overue

12/05/19 12/12/19

Paul Newcome

Are you able to provide a screenshot of your sheet with sensitive/confidential data locked, removed, or replaced with "dummy data"?

lesmickin

I exported it into excel so i could move the fields around for easier viewing.

Paul Newcome

Ok. So what determines that it is overdue?

I have gathered that it is overdue if the [Ackn Letter Date] is blank, but what are the start and end dates?

lesmickin

it's overdue if the ackn letter date is blank, let me provide a better representation.

For instance a case is opened, and they have 7 calendar days from the date assigned to send an ackn letter. I have fomulated the ackn letr due date to reflect the 7 days. for those that are blank i am needing to see how many days they are overdue from date assigned.

Paul Newcome

Ok. So if the [Ackn Letter Date] is blank and the [Ackn Letter Due Date] is in the past, then you need to know how far in the past the [Ackn Letter Due Date] actual is? If so, try this...

=IF(AND(ISBLANK([Ackn Letter Date]@row), [Ackn Letter Due Date]@row < TODAY()), TODAY() - [Ackn Letter Due Date]@row)