# How to show days overdue only when task hasnt been completed

1. ## How to show days overdue only when task hasnt been completed

So let me describe my sheet:

Column G = Deadline
Column H = Days Left to Complete
Column I = Days Overdue
Column J = Date Completed

I want to show the number of days overdue BUT only when the task is actually overdue (i.e i dont want it saying -2) AND i dont want it to show anything if the column G is blank.

So far i have managed to produce this formula which works for Days left to complete: =IF(AND(G8>TODAY(), J8=""),G8-TODAY(), "")

I have tried using this formula for days overdue but it just doesnt seem to be working how i want it too: =IF(OR(G8>TODAY(), AND(G8="", J8="")), "", G8-TODAY())

Any help would be greatly appreciated.

2. ## Re: How to show days overdue only when task hasnt been completed

This ..

for Overdue

=IF(OR(G8>TODAY(), AND(G8="", J8="")), "", TODAY()-G8)

3. ## Re: How to show days overdue only when task hasnt been completed

hmmm that still didnt seem to solve it. I feel I need another OR clause in there. I know this formula doesnt make sense excel wise but do you catch my drift??

=IF(OR(G8>TODAY(), AND(G8="", J8="")), "", TODAY()-G8) OR J8<G8, "", TODAY()-G8)

I just dont know how to integrate the OR clause thats in bold into it.

4. ## Re: How to show days overdue only when task hasnt been completed

well, i think that you could simple formula like to calculate the days left to complete using =G9-TODAY() however, your ask is hide the cell value if it negative.

in that case, we can use the conditional formatting to hide the cells with -ve values
https://www.extendoffice.com/documen...e-numbers.html

Hope this helps you out

5. ## Re: How to show days overdue only when task hasnt been completed

It seems to me that the formula could be written like this:
Formula:
`Please Login or Register  to view this content.`

Let us know if you have any questions.

6. ## Re: How to show days overdue only when task hasnt been completed

I think you cracked it! cheers pal!!!

7. ## Re: How to show days overdue only when task hasnt been completed

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

8. ## Re: How to show days overdue only when task hasnt been completed

hI JeteMc,

i found the formula doesnt remove value if it is past the target date. it only removes values if the completed date is before the due date.
can you please help to remove the value when the job is completed. Thanks

9. ## Re: How to show days overdue only when task hasnt been completed

Jusjos

Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1