Override a cell that has a formula

[Treecko]

HI Gang
I'm looking to figure the correct way to input 2 formulas in single cell base on the entry of another cell. Example is A1 has TODAY () and I want to input formula in J1=IF((A1-TODAY())<=J1,A1-TODAY()& " Past Due"," ") which works if my file is not complete. If I complete the assignment I could enter my final $ amount in H1. When that is done I would like to have the J1 cell redirect to formula =IF(H1<>"",IF(J1="",TODAY(),J1),"") which would turn changes from over due to today's date when I complete the assignment. Clear as mud, I hope??
I can upload a test sheet if you would like.
Thanks

[FDibbins]

Clear as mud, I hope??

yup, pretty much lol...
if A1 contains TODAY(), then
=IF((A1-TODAY())<=J1 doesnt make sense (unless J1=0?), because TODAY()-TODAY() = 0

Aslo, you said that this formula is IN J1? But you are then testing J1=IF((A1-TODAY())<=J1
This will result in a circular error

What exactly are you trying to do?

[Treecko]

Sorry, Hope I can clear the mud FDibbins. I'v uploaded a sample sheet to help explain myself. Should have done this in my first thread.
Thanks

[FDibbins]

Thanks for the file.

When I opened it, the 1sty thing it told me was that you have a circular reference error - as I said you would get.

When you construct a formula in a cell that says...if a certain number is <= what is already in this same cell..., you hace a circ ref error.

Pretty much you are saying...does A1=A1, this is a circular reference error

Another thing I noticed is that even IF you were referencing another cell (instead of the cell where that formula resides), you are trying to compare a numeric value (A1=TODAY() with a text value (-9Past Due)...essentially you are saying is 5 <= frog
(excel always gives text a higher value than any numeric value, so the answer would always be yes, but thats besides the point)

What exactly are you trying to do? The direction you are trying to take is a dead-end

[Treecko]

Best I can explain is: I start an assignment with the date I received the file (A1), when I get a contractor's estimate for $ amount to do the job. I enter their initial $ amount in (F1). If find it is non-compliant to our standards I notify the contractor to make the changes which in (J1) tracks the overdue days. When I get the estimate back I put the revised total amount in (G1). The difference if any will total in (H1). This is when I would like to have the "past Due" to change to Today() when I complete the file. I Hope this explained it.
Regards