# Count days past from negative given start date

#### si3po

##### Board Regular
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:
Excel Formula:
``=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

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

*bump*

Replies
6
Views
107
Replies
10
Views
112
Replies
3
Views
189
Replies
10
Views
483
Replies
1
Views
412

1,127,822
Messages
5,627,096
Members
416,220
Latest member
Sykotik7S

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back