Jul 22 2020 08:57 AM
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?
Jul 22 2020 10:08 AM
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)
Jul 22 2020 10:49 AM
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.
Jul 22 2020 11:08 AM
Jul 22 2020 11:21 AM
SolutionJul 22 2020 01:43 PM
@Sergei Baklan Thanks so very much, you are like a magician!
Jul 22 2020 01:50 PM
@Aidan1700 , you are welcome
Jul 22 2020 11:21 AM
SolutionPerhaps
with
=IF(C6="Closed",MAX(0,Q6-P6),IF(C6="Expired",MAX(0,TODAY()-P6),""))