Count days past from negative given start date

[si3po]

hi all,

bit of an obscure one this, and maybe why i'm struggling with it, but here goes.

We run a 'days outstanding' report that allows us to track customer quotes and special offers, they have a 14-day period to return, plus a 14 day grace. I'm trying to create a formula that gives us almost a countdown to expiry based on the date of issue.

Let me give an example:

• The date of quotation/offer will be day -14 (negative 14) - actual date is entered in Col. E
• date of quote/offer +14 days is day 0 (zero). i.e. they should have returned the quote to us by now
• date of expiry is 28 days from date of quote issue (i.e. 14 days + 14 days grace) is day 14 (positive 14) - and therefore 14 days PAST expiry..

Real-world example: I give customer A a quote today, 7th October. Therefore in 14-days his 'return by date' expires; so we start at Day -14. In 14 days (21st Oct) we reach Day 0 and in 28 days, (4th Nov) we are at Day 14, and the days continue to count UP from there until we get a return.

with this set up i intend to use a conditional formatting rule to colour all negative numbers green, days 0 to 14 amber and 15+ red, giving a quick overview of who's late returning.

i've tried a few things already, but my formula always results in the count going DOWN from -14... e.g yesterday it was -14, today -15 tomorrow -16 etc etc

here's one of my most recent attempts to fail:

=IFS(C5<>"",(IF(TODAY()-E5>14,14-E5+TODAY(),E5-14-TODAY())),(IF(Q5<>"CANX",TODAY()-Q5,"CANX")),IF(C5="",""))

Thanks in advance - and i welcome any assistance you can offer.

Si3PO

 

[si3po]

*bump*