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.

This ..

for Overdue

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

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.

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

I think you cracked it! cheers pal!!!

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