SOLVED

New Contributor

# Past Due Date Calculation based upon Status

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?

6 Replies

# Betreff: Past Due Date Calculation based upon Status

Maybe this could fit.

Nikolino

I know I don't know anything (Socrates)

# Betreff: Past Due Date Calculation based upon Status

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.

# Betreff: Past Due Date Calculation based upon Status

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
best response confirmed by Aidan1700 (New Contributor)
Solution

# Betreff: Past Due Date Calculation based upon Status

Perhaps

with

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

# Betreff: Past Due Date Calculation based upon Status

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

# Betreff: Past Due Date Calculation based upon Status

@Aidan1700 , you are welcome