Past Due Date Calculation based upon Status

[Aidan1700]

I thought this would be a pretty easy statement to write, however I'm struggling more then I thought I would with it. 

I want to calculate Date Past Due days based upon an Expired status in a column calculated against the ETA Date. So for example, if the Start date is 5/1/20 and the ETA is 6/1/20 and today is 7/1/20 how can I show that the Past Due Date is 30 days?

If Status changes to Closed, I want the Past Due Days (if any) to stay stagnant from the day they are Closed.

If Status is set to Open, I don’t want to calculate any Past Due Days.

My Start Date is in (B6).

My Status of Open, Expired, Closed is in (C6).

My ETA Date is in (P6).

My Date Closed is in (Q6).

My Past Due Days is in (S6)

I have tried doing a few things with Conditional Formatting and IF/AND statements but cannot get anywhere. Does anyone have any idea how to save my mental health and get this done? Or is it even possible?

[#2]

@Aidan1700 

Maybe this could fit.
Please see the attached file.

If this is the answer, please tick the correct answer and a like ... if not, please give a short feedback.

Nikolino

I know I don't know anything (Socrates)

[#3]

@NikolinoDE 

Thank you Nikolino, I appreciate your help.

It only answers the question partially. I need to control it by the status "expired" and if the status "closed" I don't want it to recalculate.

[#4]

in a small sheet with the desired values and the output would be an advantage ... especially for those who can not handle english as well as some ignorant like me :)

[#5 · Accepted solution]

@Aidan1700 

Perhaps

with

=IF(C6="Closed",MAX(0,Q6-P6),IF(C6="Expired",MAX(0,TODAY()-P6),""))

[#6]

@Sergei Baklan Thanks so very much, you are like a magician!

[#7]

@Aidan1700 , you are welcome