Conditional formatting for dates

How would I achieve automatic colour changing dates with exceptions.

For example I need to be able to put in any date for applications (so they don't exist yet.) and then after 30 days i need it to go orange and then from that 30 days another 7 days it goes red but if the completed column is checked i need the conditional formatting to be invalid as it wont be necessary for the colours to change.

How do i achieve this?

column - date application received (23/02/2026) turn orange at (23/03/2026) turn red at (30/03/2026) stays white or turns white is column competed has "D"